Re: help regarding sql loader
Date: Fri, 18 Jan 2008 10:28:13 -0800 (PST)
Message-ID: <9a052ba2-a820-41d3-9e91-cd4a658e4c11@e6g2000prf.googlegroups.com>
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