Q
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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

How can I choose only those records where grade equal to a1 or a2 or a3 or a4 or a5 in SQL loader?
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close