Help with SQL Loader

[John T. Le]

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

[pipo]

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

[John T. Le]

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.

[pipo]

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

[John T. Le]

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.

[pipo]

G is a local drive, it is not mapped from another box, right ?

[John T. Le]

Yes. Our server has all together 80 GB, divided equally among the four drives, C:, E:, F:, and G:.

[pipo]

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

[sambavan]

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

[John T. Le]

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