SQL Loader error!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL Loader error!

  1. #1
    Join Date
    Nov 2000
    Posts
    162
    Hi all,

    I am trying to insert data in comma delimited file into Oracle database sitting in Unix server. After I ran the script, I got error message as,

    Ora8_Acct: sqlload scott/tigger /export/home/dbase/cat.ctl

    SQL*Loader: Release 8.0.4.0.0 - Production on Wed Apr 25 13:30:44 2001

    (c) Copyright 1997 Oracle Corporation. All rights reserved.

    SQL*Loader-930: Error parsing insert statement for column CATEG.CDATE.
    ORA-00984: column not allowed here


    Control file - cat.ctl

    load data
    infile '/export/home/dbase/cat.dat'
    append
    into table categ
    fields terminated by "," optionally enclosed by '"'
    (cat_id,lis_id,catnme,desct,ptid,stname,mdate "MM/DD/YY",
    ordpt,cdate "MM/DD/YY")


    Data File - cat.dat

    "1","1","Antennas/Base Station Equipment","","","ACTIVE","2/2/01","1","1/18/01"
    "2","1","Cable","","","ACTIVE","1/24/01","1","1/18/01"
    "3","1","Call and Voice Equipment and Systems","","","ACTIVE","1/24/01","1","1/18/01"



    I looked up the error codes, and check the data structure, still couldn't resolve it. Can anyone give me suggestions?

    Thanks

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Try putting the keyword DATE between the name of the column and the format... I am not sure that this is the
    problem, but that is how I always do it....

    (cat_id,lis_id,catnme,desct,ptid,stname,mdate DATE "MM/DD/YY", ordpt,cdate DATE "MM/DD/YY")

    hth,

    -John



  3. #3
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Okey. You have "MM/DD/YY" date format mask and values like "1/24/01". You do not have to enclose number values into "

    Sergey.

  4. #4
    Join Date
    Nov 2000
    Posts
    162
    John,

    Thanks! After adding DATE and ran the script again, I got this error,

    SQL*Loader-522: lfiopn failed for file (/export/home/dbase/cat.bad)

    Where does it go wrong?

    Thanks,
    Unna

  5. #5
    Join Date
    Nov 2000
    Posts
    162
    I removed the ", but still getting these SQL*Loader-930 and ORA-00984 error messages


  6. #6
    Join Date
    Nov 2000
    Posts
    344
    Unna,

    It sounds like now you are experiencing a problem with permission to write into the /export/home/dbase/ directory.
    SQL Loader wants to write records that it could not import to this .bad file.

    -John

  7. #7
    Join Date
    Nov 2000
    Posts
    162
    Thank you for all your help. I got it works now, and it's indeed this problem.

    Thx,
    Unna

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