Re: help regarding sql loader

Comments embedded.
On Jan 16, 10: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'
>

This is fine.

> primary key: id, occ, secid

This is your first problem. Since all columns in the primary key are NOT NULL you'll never get default values to populate them.

>
> 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 ")

Because of your primary key definition.

>
> Its just not putting default values for the null values.

'It' can't, as you cannot submit NULL values for insert into primary key columns.

> Is there some
> way this can be done?

Not with the primary key declared as it is.

> Please dont ask me to change the model. Its what
> I have to work with :(

Then your default value assignments for those columns are worthless, as they will never be used.

>
> Thanks
> Amitabh

David Fitzjarrell Received on Thu Jan 17 2008 - 10:24:43 CST