CLOB (Oracle DB) to String (XML)

[Anonymous]

Hi
We are using Talend 5.1.1 to read Oracle table (tOracleInput) and write into an XML file.
Oracle table has 2 CLOB columns (Total Columns: 10) and has only 6000 records.
The Talend job is running more than an hour just to read the data.
Currently, we are reading these datatypes as Objects and DB Type as CLOB. We have written a Java routine to convert the CLOB to String to load into XML file. This routine is taking too much time.
Can anyone please suggest what is the best way to handle CLOB objects in Talend?
Thank you for your time.
Regards,
Sangi

[Anonymous]

Hi Sangi
Here is a good use case that read ClOB type as input stream from Oracle table, and then use this input stream to write a output file.
https://community.talend.com/t5/Design-and-Development/resolved-How-to-Read-CLOB-object/td-p/78115
Please try this solution and give us your feedback.
Shong

[Anonymous]

Shong,
Thank you for your time.
The given solution is to read the complete XML file from CLOB column and then parsing it to respective columns. I have tired to do it this way once with out success.
The one I have mentioned is, raw data in a CLOB column (Not the XML data). For example: detail description of a book. We need to read this and load it into XML file.
Regards,
Sangi

[Anonymous]

Hi
Try to read this ClOB column with byte[] as datatype, CLOB as DB type on tOracleInput, and then convert it to String on tMap without routine. For example:
new String(row1.columnName)
Shong

[Anonymous]

Hi Shong,
Tried to implement the above solution. We have received the following error:
Exception in component tOracleInput_8
java.sql.SQLException: Unsupported feature
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:490)
at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(DatabaseError.java:810)
at oracle.jdbc.driver.ClobAccessor.getBytes(ClobAccessor.java:309)
at oracle.jdbc.driver.OracleResultSetImpl.getBytes(OracleResultSetImpl.java:552)

-
Sangi

[janhess]

You need to use Arrays.toString(arr) in your tMap

[Anonymous]

Hi,
We are unable to read the data from Oracle Input component keeping Byte[] as datatype and CLOB as DB datatype. Would like me to try Arrays.toString(arr) keeping Object as Datatype and CLOB as DB datatype?
Thank you for your time.
Regards,
Sangi

[Gadik]

Hi, i am experiencing same problem - i need to read CLOB value and then parse it / save to file.
1. when i use Object as datatype, CLOB as DB type on tOracleInput, the value of row1.columnname is null
2. i cannot use byte[] as datatype, CLOB as DB type on tOracleInput, the component does not compile.
Any suggestions?