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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our applications, PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.