Oracle Bulk Load

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

I'm still learning the ropes, so please forgive my novice approach.
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!

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

Thanks, Parham! I'll give it a try now.

[Anonymous]

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?

[Anonymous]

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.

[Anonymous]

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