SQL Loader -- "No such file or directory" error

I'm trying to run the following statement in Oracle 8.1:
sqlldr user/password control=loadctls/control.ctl direct=true

To be completely accurate, I'm running it in PERL on a UNIX server that I am telnetting to from my Windows 2000 client. (I telnet to the server on which resides the perl command file, the sqlldr control file, and the data file called by the control file.) I use the following script:
system("sqlldr user/password control=loadctls/control.ctl direct=true");

However, I'm getting the following message back:
'Can't exec "sqlldr": No such file or directory at runcontrol.pl line 320.'

The file "control.ctl" does exist and is in the "loadctls" directory. (I've also tried using the full directory path in identifying the control file and in the datafile within the control file, but the error message does not change.)

I do have the SQLLDR.EXE file in my "C:\oracle\ora81\bin" directory, which is in my Windows "path", but have not run sqlldr before.

How do I get my system to recognize and run the sqlldr statement?

I appreciate your help.

sjw

Iam not sure... Do we have DIRECT=TRUE option in 8.1 release?

Is it possible some of your environment variables are wrong and it can't find sqlldr. Try using the full path for sqlldr.

make sure your ORACLE_HOME variable is set correctly in your profile.

Thank you, balajiyes. I believe that DIRECT=TRUE is valid in 8.1 (although I am relatively new to Oracle). (I am using 8.1.7 if that makes any difference.)

My understanding is based on a quick google search and that my supervisor (out of the country for 2 weeks) ran the same sort of sqlldr script on his box (also using 8.1).

In my google search I found the following. (Perhaps you had been using different versions of Oracle on client and server?? If so, it may explain your questioning whether v8.1 supports DIRECT=TRUE.) It is from http://www.csis.gvsu.edu/GeneralInfo...96652/ch09.htm

Quote:

---

For versions of the Oracle database server prior to 9i, you can only perform a SQL*Loader direct path load when the client and server are the same version. This also means that you cannot perform a direct path load of Oracle9i data into a database of an earlier version. For example, you cannot use direct path load to load data from a release 9.0.1 database into a release 8.1.7 database.

However, beginning with Oracle9i, you can perform a SQL*Loader direct path load between different versions as long as both the client and server are version 9i or later. For example, you can perform a direct path load from a release 9.0.1 database into a release 9.2 database.

---

Thanks for your reply. I appreciate the help and hope we can get a solution.

Thanks for the posts, lesstjm.

I've tried using the full UNIX directory path in identifying the control file in sqlldr and also in the datafile within the control file, but the error message does not change.

I also made sure that SQLLDR.EXE is in my windows client system variables' path. (I don't know of any other system varaibles to change that would help here.)

I am not sure how to set or check the settings of my ORACLE_HOME variable in my profile. How would I do this?

Thanks again,
Steve

I went to my registry and found ORACLE_HOME in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. The value of this variable is "C:\oracle\ora81" which is also in my Windows client path (along with "C:\oracle\ora81\bin" which contains my SQLLDR.EXE file).

I don't know if that helps.

Thanks again.

Still trying to get this to work,
Steve

bu tyou say the perl script is on your unix server, what would you mess about with windows settings? the sqlldr executanle in on the unix system not the windows system.

In the script put the full path to the unix sqlldr, see if that works

I know very little about PERL but I am familiar with the UNIX environment.

Once you telneted to the UNIX site your registry and SQLLDR.EXE file on Windows become irrelevant. The issue now is the environment within (or maybe around) your PERL call.

When setting up the environment on the UNIX side I normally set an ORACLE_HOME, I set my PATH to include the ${ORACLE_HOME}/bin directory, I set my LD_LIBRARY_PATH to include the Oracle libraries and I set my ORACLE_SID so that it goes to the database I want.

I don't see anything corresponding to these steps in your posting.

Are you setting them before you invoke PERL? Are they being set automatically in PERL? Are they simply being left out?

What are you getting from PERL in this case that you wouldn't get from just setting your UNIX environment and issuing the call?

Thank you Davey and Marek,

Davey, I have tried to put the full UNIX path within the sqlldr statement, but still get errors.

Marek, I believe that you are right at the root of the problem. How do you set all these environment variables?

I do have the following statement early in the code which declares my SID, User, and password:
$db = DBI->connect("dbi:Oracle:host=$dbhost;sid=$sid","$dbuser","$dbpass",{PrintError => 0, RaiseError=> 0})

As far as setting my paths (PATH, LD_LIBRARY_PATH) on the UNIX side, I haven't got a clue. What value of "${ORACLE_HOME}/bin" should I use? (I know my ORACLE_HOME in Windows, but not in UNIX. My PATH in UNIX now is just "/usr/bin:") I have the same questions about the LD_LIBRARY_PATH you mentioned: What value should I use and how can I set it?

>What are you getting from PERL in this case that you wouldn't get
>from just setting your UNIX environment and issuing the call?

There is a lot more code in the Perl script which performs quite a bit of processing both before and after this sqlldr statement. They want a "one-click" solution.

Thanks so much for your help,
Steve

I think it might be a good time to turn to your local help resources. Who wrote the rest of the PERL stuff that you are using? Can you talk to them about it? Your local DBA? How about the "They" who want a one click solution?

You're right my environment was not set adequately. (I did not have an ORACLE_HOME value in my UNIX path.) Now my path is looking in the right place and the script is running!

Thanks so much for all the help!

Steve