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

I was getting a blank screen. No errors, no exceptions, nothing. Checked in the logs but I did not get any errors there too. Only thing what I can see is data is not able to insert into database, So checking if I am missing something else — user3440782, Jun 07 '19 at 15:31

score 2 · Answer 1 · answered Jun 07 '19 at 14:53

2

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.

answered Jun 07 '19 at 14:53

Tomalak

332,285
67
532
628

Thank you for your response, I dont see much difference even after adding the variables you specified – user3440782 Jun 07 '19 at 15:14
@user3440782 What do the contents of those variables tell you? What is the `cfexecute` call doing? Do you get an error message? – Shawn Jun 07 '19 at 15:22
– user3440782 Jun 07 '19 at 15:28
What are the contents of the two variables when you dumped them (`qry` and `qryerr`)? – SOS Jun 07 '19 at 16:29
@Ageax I got qryerr dump as: Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL – user3440782 Jun 07 '19 at 16:48
That's the error your shell call produces, solve it. It's at least it's an error you can search for on the web. – Tomalak Jun 07 '19 at 17:10
1

Does your ColdFusion user have permissions to Execute that file? – Shawn Jun 07 '19 at 18:13

Insert data into database using sqlldr in coldfusion

1 Answers1