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

Comments embedded.
On Jan 16, 10:36*pm, amitabh.me... (AT) gmail (DOT) com wrote:
This is fine.

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

Because of your primary key definition.

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

Not with the primary key declared as it is.

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


David Fitzjarrell

Comments embedded.
On Jan 16, 10:36*pm, amitabh.me... (AT) gmail (DOT) com wrote:
This is fine.

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

Because of your primary key definition.

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

Not with the primary key declared as it is.

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


David Fitzjarrell

Comments embedded.
On Jan 16, 10:36*pm, amitabh.me... (AT) gmail (DOT) com wrote:
This is fine.

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

Because of your primary key definition.

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

Not with the primary key declared as it is.

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


David Fitzjarrell

Comments embedded.
On Jan 16, 10:36*pm, amitabh.me... (AT) gmail (DOT) com wrote:
This is fine.

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

Because of your primary key definition.

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

Not with the primary key declared as it is.

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


David Fitzjarrell

On Jan 16, 11:36*pm, amitabh.me... (AT) gmail (DOT) com wrote:

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

On Jan 16, 11:36*pm, amitabh.me... (AT) gmail (DOT) com wrote:

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

On Jan 16, 11:36*pm, amitabh.me... (AT) gmail (DOT) com wrote:

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

On Jan 16, 11:36*pm, amitabh.me... (AT) gmail (DOT) com wrote:

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