Prev: Clear case developer position in Roseville, CA
Next: partitioning table with sequence made in java base 36
From: amitabh.mehra on 16 Jan 2008 23:36
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
From: fitzjarrell on 17 Jan 2008 11:24
Comments embedded.
On Jan 16, 10:36 pm, amitabh.me...(a)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
From: Ed Prochak on 18 Jan 2008 13:28
On Jan 16, 11:36 pm, amitabh.me...(a)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
 | 
Pages: 1
Prev: Clear case developer position in Roseville, CA
Next: partitioning table with sequence made in java base 36