Re: help regarding sql loader

On Jan 16, 11:36 pm, amitabh.me..._at_gmail.com wrote:
> Hi
>
> I have a table Test:
>       id       number     default -999
>       occ varchar2(25) default 'student'
>       addr varchar2(25)
>       qual   varchar2(100)
>       secid  varchar2(25) default 'h-000'
>
> primary key: id, occ, secid
>
> I am using sql loader to load data into the table. The ctrl file is :
> LOAD DATA
> INFILE 'C:/record_dir/log/bad/out/test.dat'
> APPEND INTO TABLE Test
> FIELDS TERMINATED BY ','
> OPTIONALLY ENCLOSED BY '|'
> TRAILING NULLCOLS
> (ID CHAR( 40 ),OCC, ADDR, QUAL, SECID CHAR(25))
>
> My test input is something like:
> 1,asst,abc,xyz,,
> 2,asst,abc,xyz,,
> ,,klm,abc,xyz,,
>
> The problem is that sql loader throws error in log file:
> Record 1: Rejected - Error on table TEST, column SECID .
> ORA-01400: cannot insert NULL into ("TEST"."SECID ")
>
> Its just not putting default values for the null values. Is there some
> way this can be done? Please dont ask me to change the model. Its what
> I have to work with :(
>
> Thanks
> Amitabh

You will have to assign the defaults yourself. apply som SQL to the null fields
 LOAD DATA
 INFILE 'C:/record_dir/log/bad/out/test.dat'  APPEND INTO TABLE Test
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '|'
 TRAILING NULLCOLS
 (ID CHAR( 40 ) "NVL(LTRIM(:ID),'-999')" --- similar SQL for OCC and SECID
  OCC, ADDR, QUAL, SECID CHAR(25)) why are any of those key fields in your input file empty anyway? Note that SQL*Loader may reject some records due to duplicate keys because of the default values.

HTH,
  ed Received on Fri Jan 18 2008 - 12:28:13 CST