Problem solve Get help with specific problems with your technologies, process and projects.

Specifying conditions in SQL*Loader control file using WHEN clause

I want to specify conditions in my SQL*Loader control file using the "WHEN" clause. But I haven't come across any articles specifying the use of WHEN for operators other than "=" or "<>". Does SQL*Loader support >,< and like operators to be specified in the WHEN clause. > > I am looking to do something like this > WHEN (COL1='001' or COL1='002').

SQL*Loader does have some ability to perform conditional processing on a load of a flat file. For instance, you can tell SQL*Loader to only load those records where a specific column contains a specific value. For example, the following clause:

WHEN (5) = 'q' 
will tell SQL*Loader to only load those rows that have a 'q' in the fifth column of data. SQL*Loader cannot do all the conditional processing that many people want. It's capabilities are limited.

Personally, I find it easiest to just load the data into a staging table and then use SQL statements to perform my complex analysis and decide how to load it. I typically just use SQL*Loader just to get the data into the database. I then use SQL statements to get it loaded into the right spots under the right conditions.

The Oracle documentation has plenty of examples on how to load data in various ways. The Oracle 9i Utilities guide includes a section for SQL*Loader on loading records based on a condition. Chapter 10 of this guide even gives case studies to help you out. You can find a copy of this doc at the following URL: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/toc.htm

For More Information

Dig Deeper on Oracle database design and architecture