topic Re: [resolved] insert blob in Oracle component in Talend Studio

[resolved] insert blob in Oracle component

Anonymous — Sat, 16 Nov 2024 12:55:13 GMT

Hi,
I have an oracle input and oracle output with the same schema...
A problem arise when tried to transfer blob column between the oracle components.
I try all propositions founded on internet but none work.
If anyone have solution please let me know
Best regards

Re: [resolved] insert blob in Oracle component

Anonymous — Tue, 17 May 2011 07:06:17 GMT

Hi
On the schema of input/output component, set type as byte[] and DB type as Blob.
Best regards
Shong

Re: [resolved] insert blob in Oracle component

Anonymous — Tue, 17 May 2011 08:44:45 GMT

Thanks you Shong...
I have done what you suggest but I have an error in the Oracle input :
Exception in component tOracleInput_21
java.sql.SQLException: Fonction non prise en charge
Thanks in advance

Re: [resolved] insert blob in Oracle component

Anonymous — Tue, 17 May 2011 09:51:45 GMT

Hi
Please upload some screenshots of job, so we can help you further.
Best regards
Shong

Re: [resolved] insert blob in Oracle component

Anonymous — Tue, 17 May 2011 14:39:33 GMT

this is some of screeshots :

Re: [resolved] insert blob in Oracle component

Anonymous — Tue, 17 May 2011 14:46:51 GMT

there are something strange in my process :
In the oracle Input When the schema matches the query there is an error on it :

Exception in component tOracleInput_1
java.sql.SQLException: Fonction non prise en charge

But when the query provide much colomns than the query there is not an error, the rows are transmitted
in the output but no rows are inserted in the database table....
I think that there is no Oracle BLOB support in Talend...Maybe I miss something
Nas
Best Regards

Re: [resolved] insert blob in Oracle component

Anonymous — Wed, 18 May 2011 07:14:40 GMT

In the oracle Input When the schema matches the query there is an error on it :

Please upload a screenshot of schema of tOracleInput and show us your query, the problem occurs on tOracleInput component.

But when the query provide much colomns than the query there is not an error, the rows are transmitted
in the output but no rows are inserted in the database table....

Do you use tOracleConnection to create a db connection or create the db connection on tOracleOutput?

I think that there is no Oracle BLOB support in Talend...Maybe I miss something

No, Talend support Blob type, I tested and it works!

Best regards
Shong

Re: [resolved] insert blob in Oracle component

Anonymous — Wed, 18 May 2011 08:51:51 GMT

Please upload a screenshot of schema of tOracleInput and show us your query, the problem occurs on tOracleInput component.

The query is :
"SELECT A.DESCRLONG
FROM mytable A
"

Do you use tOracleConnection to create a db connection or create the db connection on tOracleOutput?

I use tOracleConnection to create a db connection
This is screenshot of schema of tOracleInput :

Re: [resolved] insert blob in Oracle component

Anonymous — Wed, 18 May 2011 09:06:37 GMT

I use tOracleConnection to create a db connection

If you use tOracleConnection in the job, you should use a tOracleCommit to commit the changes and close the connection. eg:
tOracleConnection
|
onsubjobok
|
tOracleInput-->tOracleOutput
|
onsubjobok
|
tOracleCommit
Best regards
Shong

Re: [resolved] insert blob in Oracle component

Anonymous — Wed, 18 May 2011 10:05:04 GMT

If you use tOracleConnection in the job, you should use a tOracleCommit to commit the changes and close the connection

That's what I do
The problem I excpect now is :
Phase 1 : strange thing
I change the db type blob to clob and talend type to string for both
No error, rows are transmitted between the jobs but when I do "select..." on sqldevelopper
no rows are returned.
Phase 2 : Another strange thing
I try to insert directly on sqldevelopper it works but my primary key id which it's autoincremented don't begin at 1.
It's like rows are inserted and after are removed.
Phase 3 : End
After insert on sqldevelopper I relaunch my process (take into account that my oracle output empty the table before insertion)
I check my table on sqldev no rows are inserted no rows are deleted
I insert again on sqldevelopper and again the id are incremented by the rows count transmitted on my process
I don't know if it's my db or talend which don't do things well......
I remember you my process are minimal and only 100 rows are transmitted
To make sure that rows are transmitted I use a tlogrow component to check the values and that's ok...
Then What do you think about that ??

Re: [resolved] insert blob in Oracle component

Anonymous — Wed, 18 May 2011 13:29:16 GMT

issue reloved when change blob to clob
Thanks