topic Re: [resolved] tOracleBulkExec not loading data into table in Talend Studio

[resolved] tOracleBulkExec not loading data into table

_AnonymousUser — Sat, 16 Nov 2024 11:36:52 GMT

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

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Tue, 17 Jun 2014 07:29:34 GMT

Hi,
Could you please take a look at forum https://community.talend.com/t5/Design-and-Development/tOracleBulkExec/td-p/62079 to see if it is helpful for your issue?
Best regards
Sabrina

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Tue, 17 Jun 2014 18:58:55 GMT

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.

Thank you,
-Humberto

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Wed, 18 Jun 2014 21:31:44 GMT

Any other ideas as to what could be wrong with the toraclebulkexec component?

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Thu, 19 Jun 2014 03:58:18 GMT

Hi
A quick quesiton, is the job running in Talend Studio or Job Server?
Shong

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Thu, 19 Jun 2014 16:13:32 GMT

Hi Shong,
The job is running in jobServer (Linux Box).
Thanks,
-Humberto

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Fri, 20 Jun 2014 10:16:14 GMT

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

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Tue, 24 Jun 2014 15:54:18 GMT

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?

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Wed, 25 Jun 2014 06:36:09 GMT

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

Re: [resolved] tOracleBulkExec not loading data into table

Anonymous — Mon, 30 Jun 2014 15:38:25 GMT

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