-
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
-
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.
-
You can use CONSTANT keyword such as:
column CONSTANT value
in the control file. SQL*Loader will populate the column with the constant value.
-
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.
-
oops !! how to disable the not null constraints in the table ??
-
Example
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]
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|