Help for sqlldr problem

Hi ,

I want to test how to load data like the situation.

SQL> desc temp

 Name                     Null?    Type
 ------------------------------- -------- ----
 COL1                                     NUMBER
 COL2                                     CHAR(4)
 COL3                                     CHAR(4)

SQL> sqlldr control file (test.ctl) :
LOAD DATA
REPLACE
INTO TABLE temp
(

col1                POSITION (001:003) CHAR,
col2                POSITION (005:008) CHAR,
col3                POSITION (010:013) CHAR

sqlldr command:
sqlldr scott/tiger control=test.ctl data=test.dat log=test.log

And test data (test.dat) :
1 AA AA
2 BB BB
3 CC CC

And of course it is loaded completely,
SQL> select * from temp;

      COL1 COL2 COL3
---------- ---- ----

         1 AA   AA
         2 BB   BB
         3 CC   CC 

But my situation is , IF in test.dat , COL1 is empty , (position 001:003 are blanks) and I want to pass in an variable for deciding the COL1's value.

For example , if :var1>=1 and :var1 <=5 , then COL1 = 1. if :var >= 6 and :var1 <=10 , then COL1 = 2 , if :var1>=11 and :var1<=15 , then COL1 = 3 So maybe COL1 is 1 or 2 or 3 , but it is decided according to :var1.

But I don't know how to define and pass a variable in sqlldr? Or how can I do for this?

Thanks in advance!

Violin.
violin.hsiao_at_mail.pouchen.com.tw Received on Wed Jan 02 2002 - 03:49:23 CST