sql* loader question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: sql* loader question

  1. #1
    Join Date
    Feb 2001
    Posts
    114
    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



  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    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.

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    You can use CONSTANT keyword such as:

    column CONSTANT value

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

  4. #4
    Join Date
    Feb 2001
    Posts
    114
    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.

  5. #5
    Join Date
    Feb 2001
    Posts
    114
    oops !! how to disable the not null constraints in the table ??

  6. #6
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking 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]

  7. #7
    Join Date
    Feb 2001
    Posts
    114
    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
  •  


Click Here to Expand Forum to Full Width