Re: help regarding sql loader
Date: Thu, 17 Jan 2008 08:24:43 -0800 (PST)
On Jan 16, 10:36 pm, amitabh.me..._at_gmail.com wrote:
> 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:
> 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.
David Fitzjarrell Received on Thu Jan 17 2008 - 10:24:43 CST