One Star

Oracle Bulk Load

Hi,
Does anyone have a sample job for Oracle Bulk Collect and Bulk Load?
On another note, my environment variables are set up correctly, however I'm still receiving the following error message.
Exception in component tOracleBulkExec_1
java.io.IOException: Cannot run program "sqlldr": java.io.IOException: error=2, No such file or directory
at java.lang.ProcessBuilder.start(ProcessBuilder.java:459)
at java.lang.Runtime.exec(Runtime.java:593)
at java.lang.Runtime.exec(Runtime.java:431)
at java.lang.Runtime.exec(Runtime.java:328)
at talendpractice.bulkcollecttest.BulkCollectTest.tOracleBulkExec_1Process(BulkCollectTest.java:545)
at talendpractice.bulkcollecttest.BulkCollectTest.tOracleInput_1Process(BulkCollectTest.java:436)
at talendpractice.bulkcollecttest.BulkCollectTest.runJobInTOS(BulkCollectTest.java:747)
at talendpractice.bulkcollecttest.BulkCollectTest.main(BulkCollectTest.java:666)
Caused by: java.io.IOException: java.io.IOException: error=2, No such file or directory
at java.lang.UNIXProcess.(UNIXProcess.java:148)
at java.lang.ProcessImpl.start(ProcessImpl.java:65)
at java.lang.ProcessBuilder.start(ProcessBuilder.java:452)

Any ideas?
Thanks.
14 REPLIES
Community Manager

Re: Oracle Bulk Load

Hello
java.io.IOException: Cannot run program "sqlldr": java.io.IOException: error=2, No such file or directory

Please upload a screenshot of your job.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Oracle Bulk Load

I'm still learning the ropes, so please forgive my novice approach. Smiley Happy
I've set the file to be located at "/tmp/test_load.dat" and when the job runs, I can tail the file and see the list being populated. However when it attempts to load it, that's when it dies.
Thanks for any help!
One Star

Re: Oracle Bulk Load

Perhaps it's complaining because it cannot find the executable for the Oracle bulk load? Perhaps it's not installed? Or it's not in the path?
I don't use Oracle - just trying a somewhat logical guess here.
One Star

Re: Oracle Bulk Load

All the environment variables are set, and it is installed on the host it executes on. Confirmed that it can be executed manually (sqlldr). Thanks for your thoughts, though.
Employee

Re: Oracle Bulk Load

From your log file indicated below it seems like the problem is with a column called "ODATE". loading date columns was a problem in previous versions. This has been fixed in version 2.4. Also there is fix with regards to previous version. Please refer to this post:
1456
Also refer to:
3644
COPY FROM YOU .ctl LOG FILE:
value used for ROWS parameter changed from 64 to 35
Record 1: Rejected - Error on table SOE_WHS.ITEM_RECONCILIATION_FACT, column ODATE.
ORA-01858: a non-numeric character was found where a numeric was expected
One Star

Re: Oracle Bulk Load

Thanks, Parham! I'll give it a try now. Smiley Happy
One Star

Re: Oracle Bulk Load

Hey Parham,
I've downloaded TOS 2.4.0.r14858 and have created a simple bulk load job, but it seems to come up with the same error as before regarding handling the date.
Record 1: Rejected - Error on table ORDERS, column ODATE.
ORA-01858: a non-numeric character was found where a numeric was expected

My job is simply as follows:
tOracleInput -> tOracleOutputBulkExec
Any ideas?
Employee

Re: Oracle Bulk Load

Go to the advanced settings tab of the tOracleOutputBulkExec component and make sure that you have enabled the "Use schema's Date Pattern to load Date field" option.
Please take a look at this screen shots below.
One Star

Re: Oracle Bulk Load

Hi Parham:
I have made sure that the Advanced option of using the schema's Date Pattern to load Date field has been checked. Unfortunately, I am still receiving the error. I have attached a screenshot of my test job in TOS 2.4. My source input should be able to be a tOracleInput -> tOracleOutputBulkExec, correct?
Are you able to reproduce the error with the db as a source? Also to note, is that I am exporting the job scripts and uploading them to our execution server. Perhaps that might help in reproducing the error as well?
Thanks for your help,
Warren
Employee

Re: Oracle Bulk Load

can you make sure in the schema of the tOracleOutputBulkExec component you have the right Date Pattern for the ODATE column. The component should use this pattern for the sql loader. In any case seems like the sql loader is expecting the integer representation of the ODATE instead of a valid date pattern. In any case we should be able to transfer the ODATE into the desired integer form that oracle sql loader would accept using a tMap. Can also check to see which version of the sql loader you are using and also please attach the .ctl file created by talend.
thanks,
parham
Employee

Re: Oracle Bulk Load

hi !
check if your control file is in correct extension (ctl ans not clt)
regards
One Star

Re: Oracle Bulk Load

Hello
I have the same problem. Have you found a solution ?
Thx
One Star

Re: Oracle Bulk Load

I have the same problem. Is there a fix to this problem?
One Star

Re: Oracle Bulk Load

we set the PATH and ORACLE_HOME environment vailables on the windows server to resolve the issue