[resolved] insert blob in Oracle component

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

this is some of screeshots :

[Anonymous]

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

[Anonymous]

Hi

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

[Anonymous]

Hi

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 :

[Anonymous]

Hi

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

[Anonymous]

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