urgent, sqlldr question....

[thg]

hi guys,

I have a question regarding the sqlldr in oracle. It is about the control file. I have a input .csv file, and I would like to load the csv column 'COUPON' to two of my database columns, let's say 'INTEREST_RATE' and 'COUPON'. is it possible to do that in sqlldr? How should the control file look like? Please take a look at what I have here.




my input file: 'data190.csv'
----------------------------------------------


DEAL_NUM, LOAN_NUM, COUPON
MyTest,100,8.7
MyTest2,201, 9.3


MY control file
---------------------

LOAD DATA
INFILE 'data190.csv'
REPLACE
INTO TABLE LOAN_TEMP
fields terminated by ','  TRAILING NULLCOLS
(
        DEAL_NUM CHAR NULLIF DEAL_NUM=BLANKS,
        LOAN_NUM INTEGER EXTERNAL  NULLIF LOAN_NUM=BLANKS,
        COUPON DECIMAL EXTERNAL  NULLIF LOAN_WAC=BLANKS,
        INTEREST_RATE DECIMAL EXTERNAL  NULLIF LOAN_WAC=BLANKS

)

In the data file, I have 3 columns but in the control file I have 4 columns, so I don't think the control file above will work. Anyone has an idea on how to handle this kind of problem?

Thanks.

[MJP]

Add INTEREST_RATE column to your .csv file as 4th column. It should work.

[thg]

I know, but I couldn't add columns to the data file because the data file is going to be uploaded when no one is present.


The users define how they want to translate the csv columns to database column through a front end GUI. Sometimes, they may want to put the same data to different columns. They defined the translation only for the first run. After that, all uploads will be done in a job scheduler using the same translation everyday at 5:00AM in the morning. And when they go to the office in the morning, they will see a report generated by the program.

[jmodic]

How about adding a simple before insert row trigger on the table?

CREATE OR REPLACE TRIGGER loan_temp_bir_trigg BEFORE INSERT ON loan_temp
FOR EACH ROW
BEGIN
  IF :NEW.interest_rate IS NULL THEN
    :NEW.interest_rate := :NEW.coupon;
  END IF;
END;

Of coures, if using this technique, you should leave your column INTERST_RATE out of your control file.

[tomate]

hi there,
jmodic has right, but that it doesn't work as direct load