Help with SQL Loader
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Help with SQL Loader

  1. #1
    Join Date
    Oct 2001
    Location
    Atlanta, GA
    Posts
    22
    Good day to you all:

    I am having some problems using the SQL Loader utility. I am trying to load some data that were sent to us by our client. Went into command prompt and issued the following command:

    Sqlldr userid=user/pswd data.clt data=data.dat

    but was given the following error messages:

    SQL*Loader 500-unable to open file (data.ctl)
    SQL*Loader 553-file not found
    SQL*Loader 509-system error, unable to locate specified file.

    I then tried the following command:

    Sqlldr userid=user/pswd control=data.ctl

    and was given the same error message. I even tried to use the absolute path in both the command & control file, but to no avail.

    the control file is as follow:

    Load Data
    infile data.dat
    into table new_data
    replace
    fields terminated by ","
    (col1,col2,col3,col4)

    Both the data & control files were loaded onto the same temp folder in one of our partitioned drives. The destination tables are also on the same server, however under different drive.

    Anyone has any idea what I did wrong, and how I can get this resolved. I have deadline to meet and in a rather dire situation. Thank you in advance for any helpful advice, help, and suggestion.

    J.T

    P.S: We use Oracle 8.16 running on Win NT 4.0

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    if you specify full path for your ctl file, you should not get the same error again (unable to open file (data.ctl)) ... if you still get this one, go and check if your file exists, is in the right directory, and has the right name

  3. #3
    Join Date
    Oct 2001
    Location
    Atlanta, GA
    Posts
    22
    I did use the full path in the command, i.e

    sqlldr userid/pswd control=G:\temp\data.ctl.

    I also tried to absolute path the source data in the control file, i.e:

    infile (G:\temp\data.dat)

    but no matter what I try, I kept on getting the same messages.

    I used NT Explorer to make sure the data & control files are there, and were able to confirm their existence. I even use the "Find" command under the "Start" menu and it was able to locate the above mentioned files. It seems everything is there, but I am not sure why sql Loader is not able to locate the files, and kept on giving me the same messages.

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    there is a bug I found on Metalink, they say SQL*Loader meeses up with / and \ in the paths, and the workaround is to make a NT/Unix mix, try :

    control=G:/temp\data.ctl
    and infile G:/temp\data.dat

  5. #5
    Join Date
    Oct 2001
    Location
    Atlanta, GA
    Posts
    22
    Hi Pipo:

    Thank you for all the help. I just tried what you've suggested but still getting the same three error messages mentioned earlier.

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    G is a local drive, it is not mapped from another box, right ?

  7. #7
    Join Date
    Oct 2001
    Location
    Atlanta, GA
    Posts
    22
    Yes. Our server has all together 80 GB, divided equally among the four drives, C:, E:, F:, and G:.

  8. #8
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    well, I have no answer ...
    here is the complete article I found on Metalink, which is the only one referencing your errors :


    Problem Description
    -------------------

    Trying to use the UNC (Universal Naming Convention) for a path and
    file name in SQL*Loader produces results similar the following:

    sqlldr scott/tiger@local data=\\somehost.abc.com\x\test.dat
    bad=test.bad log=test.log control=test.ctl discard=test.dsc

    This fails with the following errors:

    SQL*Loader-500: Unable to open file (\somehost.abc.com\x\test.dat)
    SQL*Loader-553: file not found
    SQL*Loader-509: System error: The system cannot find the file specified.


    Solution Description
    --------------------

    There are problems with how SQL*Loader parses path names trying to use
    UNC. The work around for this is to invert the UNX path separators.
    To get the desired effect, you will have to use the following:

    sqlldr scott/tiger@local data=//somehost.abc.com/x\test.dat
    bad=test.bad log=test.log control=test.ctl discard=test.dsc

    You can use this for any of the parameters requiring a path and file
    name as in the following parameters:

    data=//somehost.abc.com/x\test.dat
    bad=//somehost.abc.com/x\test.bad
    log=//somehost.abc.com/x\test.log
    control=//somehost.abc.com/x\test.ctl
    discard=//somehost.abc.com/x\test.dsc

    Note: "somehost.abc.com" represents the Windows networking computer name.


    References
    ----------

    [BUG:1346942] UNC PATH NAMES DO NOT WORK PROPERLY WITH SQL LOADER

  9. #9
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    1. can you create the same datafile under different disk and try it?

    Don't use the copy and paste. Do a compleate recreate. Secondly log into your command prompt and traverse to the directory and confirm that the file exists. Then as the same user invoke the sqlloader and confirm the status ...

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  10. #10
    Join Date
    Oct 2001
    Location
    Atlanta, GA
    Posts
    22
    Hi Sam:

    I assume I can, but that would be too time consuming. However, base on your suggestion, maybe because of what I did with the data when I first got it that is causing all the problem. The data was initially sent to our developers in a zip file (only 300 KB when unzipped)-I then unzipped it and copied it onto a floppy disk, which is then loaded onto the G:\ drive. I did use the copy and paste method to load the data to the drive. Could this be the reason why sql loader was giving all the error messages?

    As for plan B, which I am presently contemplating, would exp/imp do the trick? Initially I did not think about this since my belief at the time was that sql loader would be a whole lot simpler and faster way to load the data.

    And Pipo, thank you so much for all the help.

    J.T

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