sql* loader question

[yenni143]

hello all,

i have a text file which is like this

74,001,AFGHANI,, - -,
74,002,TURKEY,, - -,
74,003,CHINA OAK,, - -,
74,004,IRAN,, - -,
74,005,AMIRAN OAK,, - -,
74,006,PAKISTAN HK,, - -,

the table :-

icmasqua (company varchar2(2) primary key, masqua varchar2(3) NOT NULL,
name varchar2(30) NOT NULL,
family varchar2(2) NOT NULL,
lastupd date NOT NULL,
upduser varchar2(15) NOT NULL)

how can i write a control file to load data into the not null columns if there is no data for the respective fields in the text file ??


i know this control file wont work

LOAD DATA
INFILE 'c:\icmasqua.txt'
INTO TABLE icmasqua
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(company,masqua,name,family,lastupd,upduser)


thanks

[kris109]

You can disable the NOT NULL constraint, load the data. Once loading is complete you can update the table with a default value of the column and enable the constraint.

[kris109]

You can use CONSTANT keyword such as:

column CONSTANT value

in the control file. SQL*Loader will populate the column with the constant value.

[yenni143]

hi,

how to enable the not null constraint? i dont want to load the table with any default value.
whatever is in the text file, it should load into the table.

[yenni143]

oops !! how to disable the not null constraints in the table ??

[jgmagnus]

ALTER TABLE OWNER.TABLE DISABLE CONSTRAINT constraint_name;

OR
(whoops, first one was wrong syntax)

select 'alter table '|| r_owner ||'.'||
table_name ||' disable constraint '||
constraint_name ||';'
from user_constraints
where r_owner = upper('&username') AND
table_name = upper('&table_name');


i believe 'O' is the Not Null constraint, but not sure

run the output.

- Magnus



[Edited by jgmagnus on 08-31-2001 at 03:56 PM]

[yenni143]

Disabling the constraints is not working.

again, my text file looks like this-
74,306,X MISCELLANEOUS,, - -,
74,502,CHINA TUF SYNTH,, - -,
74,504,CHINA HK,, - -,
74,307,MM MADE,, - -,
74,509,PADDING,, - -,
74,, All Master Qualities,, - -,
74,999,Unkown Other,, - -,
74,015,ROMANIAN,, - -,
74,600,AMIRAN SECONDS,, - -,
74,305,WHITNEY SECONDS,, - -,
74,910,JOVENTURE,, - -,
74,102,Chinese Full,20, - -,
74,103,Chinese Hook,20, - -,
74,202,Rugs,02, - -,
74,208,Coir Mats,02, - -,
74,209,Table,, - -,
74,210,Throws,, - -,

control file is
LOAD DATA
INFILE 'c:\tab\icmasqua.txt'
INTO TABLE today
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(company,masqua,name,family,lastupd,upduser)

the table icmasqua is
(company varchar2(2) primary key,
masqua varchar2(3) NOT NULL,
name varchar2(30) NOT NULL,
family varchar2(2) NOT NULL,
lastupd date NOT NULL,
upduser varchar2(15) NOT NULL)

if i run the sqlldr script the data is not being loaded, when the text file has null fields

pls help

thanks