Insert data into database using sqlldr in coldfusion

Question

I have a CSV file that I got from a website. I need to upload that same CSV file into my database using SQLLDR in ColdFusion. For some reason I'm not able to insert the data into database.

Below is my code. Using this code I was not able to insert the data into database from the CSV file. It is working file from a batch file, but it is not working using cfexecute. By that I mean, I'm getting a blank screen. No errors, no exceptions, nothing. Checked in the logs but I did not get any errors there either. Only thing what I can see is that the data is not inserted into the database.

FYI, we are using Linux environment, so the path is slightly different.

<cfset CTLPATH="/home/mosuser/apps/nodal/ctl">
<cfset LOGPATH="/home/mosuser/apps/nodal/logs">

<cfexecute name="/opt/oracle/product/12.1.0/client_1/bin/sqlldr" 
    arguments="userid/password@Sid control=#CTLPATH#/mpimReport.ctl 
    log=#LOGPATH#/#PathfileName#_load.log data=#filelist##PathfileName#.csv 
    bad=#LOGPATH#/#PathfileName#_error.txt">
</cfexecute>

Update:

As suggested, dumping the error variable qryerr showed:

Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL

Answer 1

Add a few parameters to your <cfexecute> call.

• timeout - in the order of the number of seconds expect the process to take
• variable - the name of the variable to hold the STDOUT output of sqlldr
• errorVariable - name of the variable to hold the STDERR output of sqlldr

After doing that you can get the output of the call and inspect it for any error messages and other info.

Adding the timeout is the crucial step - this makes cfexecute block until either the program terminates or the timeout is reached. Without a timeout, ColdFusion simply kicks off the process and immediately continues executing the rest of the current page.