cfexecute and sqlldr problem

[alecken]

I use cfexecute to run sqlldr in windows seccessfully. I use the same technique to do it in unix and it failed. Have debugged the problem for almost one week with no result

Oracle client has been installed on the server, path to the control file, source file have been checked and all are good but once cfexecute is run, the error pop up. I don't know what else to do

Found similar problem on the web, followed it and still did not work:

http://www.justskins.com/forums/cfexecute-and-sqlldr-run-56805.html

Here is the only code to run sqlldr that's been giving me a headache:

<cfexecute name="sqlldr" arguments="DEMO@DEMO/pwd2011 control=/opt/coldfusion8/runtime/bin/my_ControlFile.ctl" timeout="500"></cfexecute>

Here is the error I got:

The following information is meant for the website developer for debugging purposes. Error Occurred While Processing Request

An exception occurred when invoking an external process.

The cause of this exception was that: java.io.IOException: Cannot run program "sqlldr": error=2, No such file or directory.

30 :       <CFFUNCTION name="RunSQLLDR"> 31 :       32 :           <cfexecute name="sqlldr" arguments="DEMO@DEMO/pwd2011 control=/opt/coldfusion8/runtime/bin/my_ControlFile.ctl" timeout="500"></cfexecute> 33 :       34 :       </CFFUNCTION>

  Can anyone who have successfully use cfexecute for running sql loader on Oracle 11 help me?

[Owainnorth]

Use the full path to sqlloader, the error message clearly says it can't find it.

[alecken]

That is the full path, it's in unix

[Owainnorth]

Eh? "sqlldr" isn't a full path.

[alecken]

Oh sorry I thought you were referring to the path for the control file.

So you mean the path for sqlldr? I need to ask the server guy for that. Thanks for pointing that out.

The reason I did not think I have to provide path for sqlldr is because on windows I did not provide path for sqlldr and it is working

[Owainnorth]

Yeah on Windows the Oracle bin directory will be in the path so it's not needed, it will be required for linux.

[alecken]

Thank you for pointing that out!!! I'll try to contact the server guy now

[alecken]

Just got a respond from the server guy and I tried to use a path to sqlldr as suggested:

<cfexecute name="/home/oracle/product/11.1.0/cient_1/bin/sqlldr" arguments="DEMO@DEMO/pwd2011 control=/opt/coldfusion8/runtime/bin/my_ControlFile.ctl" timeout="500"></cfexecute>

When I ran it, I did not get an error anymore but nothing got inserted into the DB (table). So in other words, sqlldr is still not running.

I checked the contorl file, the paths to the text file, the columns, everything is correct.

Anyone can help?

[Owainnorth]

Have you looked in the sqlloader log file? Just because nothing was inserted doesn't mean sqlldr isn't running, it could be a data issue.

[alecken]

Just tried to run it from command prompt and the records were inserted into the table but not through CFexecute both are using the same command prompt

[Owainnorth · Accepted solution]

Okay, try instead calling the sqlldr from a bash script, which puts the std output to a file, then call that instead. You'll then be able to see any commandline errors it throws.