How to use complex SQL statement with Oracle SQL Loader Utility

Oracle has a nice utility called SQL Loader to load textual data to the database by using command line interface. I am not going to describe the usage of SQL Loader assuming that you already know a lot about it. Instead I am going to describe how can you use complex SQL statement as a source for the column in the target table.

Assuming you have the following ctl file:

LOAD DATA

INFILE 'c: emp\demo04.dat'

INTO TABLE emp (

empno         CONSTANT 1,

ename         “select  user_name from fnd_user where user_id= :empno”,

hiredate      :HIRE_DATE....

Note the highlighted line. It just will not work, because SQL Loader cannot execute and return the result from SQL statement. But it does know how to execute functions. So we can take an advantage of build-in “decode” function in the following way



LOAD DATA

INFILE 'c: emp\demo04.dat'

INTO TABLE emp (

empno         CONSTANT 1,

ename         “decode(1,1,(select  user_name from fnd_user where user_id= :empno))”,

hiredate      :HIRE_DATE ......


That’s it. You cheated Oracle, because decode is able to execute sql statements and use its return value as his own result.

Decode is a conditional function, so in order this SQL statement be executed all the time I put a condition if 1=1 which is obviously  always true