[resolved] tOracleBulkExec not loading data into table

One Star

[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

Accepted Solutions

Re: [resolved] tOracleBulkExec not loading data into table

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
8) 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

All Replies
Moderator

Re: [resolved] tOracleBulkExec not loading data into table

Hi,
Could you please take a look at forum http://www.talendforge.org/forum/viewtopic.php?id=23729 to see if it is helpful for your issue?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Re: [resolved] tOracleBulkExec not loading data into table

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

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

Re: [resolved] tOracleBulkExec not loading data into table

Hi
A quick quesiton, is the job running in Talend Studio or Job Server?
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: [resolved] tOracleBulkExec not loading data into table

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

Re: [resolved] tOracleBulkExec not loading data into table

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: [resolved] tOracleBulkExec not loading data into table

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?
Community Manager

Re: [resolved] tOracleBulkExec not loading data into table

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: [resolved] tOracleBulkExec not loading data into table

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
8) 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