[resolved] tOracleBulkExec not loading data into table
Hi,
We've had a Talend job which gets data from a .csv file and inserts it to a table that used to work up until last week. Coincidentally when we renewed our license but I don't know if that is the cause.
The job fails with the following error:
Exception in component tOracleBulkExec_1
java.io.IOException: Cannot run program "sqlldr": error=13, Permission denied
at java.lang.ProcessBuilder.start(ProcessBuilder.java:1041)
at java.lang.Runtime.exec(Runtime.java:617)
at java.lang.Runtime.exec(Runtime.java:450)
at java.lang.Runtime.exec(Runtime.java:388)
at dts_dm.rejectfiletorejecttable_0_1.RejectFiletoRejectTable$1tOracleBulkExec_1Thread.run(RejectFiletoRejectTable.java:2787)
at routines.system.ThreadPoolWorker.runIt(TalendThreadPool.java:159)
at routines.system.ThreadPoolWorker.runWork(TalendThreadPool.java:150)
at routines.system.ThreadPoolWorker.access$0(TalendThreadPool.java:145)
at routines.system.ThreadPoolWorker$1.run(TalendThreadPool.java:122)
at java.lang.Thread.run(Thread.java:744)
Caused by: java.io.IOException: error=13, Permission denied
at java.lang.UNIXProcess.forkAndExec(Native Method)
at java.lang.UNIXProcess.<init>(UNIXProcess.java:135)
at java.lang.ProcessImpl.start(ProcessImpl.java:130)
at java.lang.ProcessBuilder.start(ProcessBuilder.java:1022)
... 9 more
Here's what I've done:
1) Recycled the Talend components (commandline, jobServer, TAC running on a linux server), reran the job (failed)
checked the permissions (744) for the directory where the sqlldr is reading the file csv file and writing the log file to. 2) The directory owner is the same user as the one used to start the Talend components it is open to the user
3) I've made sure sqlldr is in the path, permissions 777. I can run the sqlldr native command successfully, which means sqlldr is working.
The directory is open and sqlldr is accessible. What could be causing the issue?
My thoughts are that the user that the tOracleBulkExec is using to run the sqlldr command is different than the user we started the Talend Components (commandLine, jobServer, TAC) with.
Any ideas?
Thanks,
-Humberto
Shong,
Thank you for pointing me in the right direction. I've solved the issue by taking the following steps:
1)Stop the jobServer running in the BACKGROUND
cd /my_jobServer_location/jobServer
./stop_rs.shv
2) Check the status before starting it
ps -ef|grep jobServer
3) Start the jobServer in the FOREGROUND
cd /my_jobServer_location/jobServer
./start_rs.sh
4) Create a test job and add tSystem component
command name: "sqlldr"
5) Run the test job above and verify that sqlldr is recognized
6) Run the problem (original) job and verify that sqlldr is recognized
7) Stop the jobServer running in the FOREground
cd /my_jobServer_location/jobServer
./stop_rs.sh
😎 Check the status before starting it
ps -ef|grep jobServer
9) Start the jobServer in the BACKground
cd /my_jobServer_location/jobServer
nohup ./start_rs.sh &
10) Run the test job above and verify that sqlldr is recognized
11) Run the problem (original) job and verify that sqlldr is recognized
I still don't know what caused the issue. simply restarting all of the Talend components (command line, jobserver, tac) didn't fix it when I tried. But doing the steps above fixed my issue.
Thanks,
-Humberto
Sabrina,
The link provided talks about access to sqlldr. I have verified that the sqlldr command works when executed from the command like using the same user that I used to start the Talend Components. (CommandLine, jobServer, TAC).
I ran the sqlldr command from within the same directory the tOracleBulkExec is supposed to be executing from.
The data is inserted to the expected table properly. But when I run the Talend Job, it does not run successfully and ends with the error posted.
Hi Shong, The job is running in jobServer (Linux Box). Thanks, -Humberto
If the job is scheduleing to run in JobServer, ensure the Oracle client is installed on the machine where the Job Server is installed, and the user used to launch JobServer has the permission to access the CSV file, it is the user who executes the job.
Shong
Shong,
I ensured the Oracle client is installed on the machine where the Job Server is installed, and the user used to launch JobServer has the permission to access the CSV file.
I also gave 777 permissions to all of the files in the directory but the job still fails. At this point sqlldr and the files are visible to everyone (777).
Any other ideas?
Hi
Can you upload a screenshot of your job and the settings of tOracleBulkExec_1?
In addition, have a try to use a tSystem to run the sqlldr command in a Talend job and see if it works.
Shong
Shong,
Thank you for pointing me in the right direction. I've solved the issue by taking the following steps:
1)Stop the jobServer running in the BACKGROUND
cd /my_jobServer_location/jobServer
./stop_rs.shv
2) Check the status before starting it
ps -ef|grep jobServer
3) Start the jobServer in the FOREGROUND
cd /my_jobServer_location/jobServer
./start_rs.sh
4) Create a test job and add tSystem component
command name: "sqlldr"
5) Run the test job above and verify that sqlldr is recognized
6) Run the problem (original) job and verify that sqlldr is recognized
7) Stop the jobServer running in the FOREground
cd /my_jobServer_location/jobServer
./stop_rs.sh
😎 Check the status before starting it
ps -ef|grep jobServer
9) Start the jobServer in the BACKground
cd /my_jobServer_location/jobServer
nohup ./start_rs.sh &
10) Run the test job above and verify that sqlldr is recognized
11) Run the problem (original) job and verify that sqlldr is recognized
I still don't know what caused the issue. simply restarting all of the Talend components (command line, jobserver, tac) didn't fix it when I tried. But doing the steps above fixed my issue.
Thanks,
-Humberto
