One Star

[resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

I need to read an Oracle table wich contains a BLOB column and insert the value into another Oracle table in a BLOB column.
I tried every examples that are on this forum but nothing works for me.
Here are the screen shots of every part of my job. Can anyone tell me what's wrong ?
Also, I will need to read a BLOB column from an Oracle table and insert the data in multiple columns in another Oracle table. I haven't find yet how to do it. If someone has an example, it would be helpfull.
Thank you
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

I finally resolved my problems.
The "java.lang.NullPointerException" problem was because the tOracleOutput was unable to use the same connection as the tOracleInput. I just defined the connection information in the tOracle Output and the error message went away.
I also experienced another problem which was "ORA-00926: missing VALUES keyword".
By removing the tMap and linking the tOracleInput and the tOracleOutput directly, the problem is not there anymore and all the rows are inserted into the destination table.
9 REPLIES
One Star

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

Hi
I have created a job which does ETL with BLOB data type under TOS 5.0.2 and it works fine.
Judging from the images you uploaded, everything seems to be OK.
But the length of BLOB column should be 65535. It cannot be left empty.
Regards,
Pedro
One Star

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

Thank you for the quick answer.
I changed the length of the BLOB columns as you suggested but it still does not work.
I always get the following error :
Démarrage du job test_blob a 09:54 08/05/2012.
connecting to socket on port 3342
Exception in component tOracleOutput_1
java.lang.NullPointerException
at inspq.test_blob_0_1.test_blob.tOracleInput_1Process(test_blob.java:616)
at inspq.test_blob_0_1.test_blob.runJobInTOS(test_blob.java:1120)
at inspq.test_blob_0_1.test_blob.main(test_blob.java:988)
connected
disconnected
Job test_blob terminé à 09:54 08/05/2012.
It works well when I output the data into a CSV file but that's not my requirement.
Can you post images of the test you've done ?
Pascal
One Star

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

I finally resolved my problems.
The "java.lang.NullPointerException" problem was because the tOracleOutput was unable to use the same connection as the tOracleInput. I just defined the connection information in the tOracle Output and the error message went away.
I also experienced another problem which was "ORA-00926: missing VALUES keyword".
By removing the tMap and linking the tOracleInput and the tOracleOutput directly, the problem is not there anymore and all the rows are inserted into the destination table.
One Star

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

Hi
Great. Glad to know you have fixed it.
So the issues are you didn't configure the component and schema correctly.
Feel free to ask any questions at Talend Community.
Regards,
Pedro
One Star

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

If you have a tOracleConnection as the first component then you can have the input and output both using this connection.
One Star

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

Hi, I have the same issue.. I have tried all the suggestions here but nothing works.
I wanted to replicate tableA in Oracle database to tableB in another Oracle database. If I remove the blob column from the mapping, the insert works ok. If I include it, TOS 5.2.0 gives me an error message:
connecting to socket on port 3927
connected
ORA-00942: table or view does not exist
disconnected
I have verified that the table DO exist (and running a job without the blob column was successful).
Below is my configuration:
TOracleInput (TableA) -> TMAP -> TOracleOutput (TableB)
Please let me know if this is a bug or not.
Thanks!
Community Manager

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

Hi JOSEPHINE
Can you please report a new topic for your question and upload some screenshots of the job settings?
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

Hi Shong,
I really want to post a new topic on this error but I can't see any button for 'New Topic'.
I you can point me to the right location, I'll be happy to do so..
Thanks!
Community Manager

Re: [resolved] Can NOT copy BLOB from and Oracle table to another Oracle table.

Hi Shong,
I really want to post a new topic on this error but I can't see any button for 'New Topic'.
I you can point me to the right location, I'll be happy to do so..
Thanks!

The button is on the upper right corner of the page. see my screenshot.
----------------------------------------------------------
Talend | Data Agility for Modern Business