topic Re: CLOB (Oracle DB) to String (XML) in Talend Studio

CLOB (Oracle DB) to String (XML)

Anonymous — Thu, 09 May 2013 14:34:06 GMT

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

Re: CLOB (Oracle DB) to String (XML)

Anonymous — Thu, 09 May 2013 14:49:26 GMT

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

Re: CLOB (Oracle DB) to String (XML)

Anonymous — Thu, 09 May 2013 15:25:41 GMT

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

Re: CLOB (Oracle DB) to String (XML)

Anonymous — Sat, 11 May 2013 05:57:52 GMT

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

Re: CLOB (Oracle DB) to String (XML)

Anonymous — Mon, 13 May 2013 16:01:01 GMT

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

Re: CLOB (Oracle DB) to String (XML)

janhess — Mon, 13 May 2013 16:34:50 GMT

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

Re: CLOB (Oracle DB) to String (XML)

Anonymous — Tue, 14 May 2013 10:08:07 GMT

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

Re: CLOB (Oracle DB) to String (XML)

Gadik — Tue, 25 Mar 2014 08:22:32 GMT

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?