One Star

CLOB (Oracle DB) to String (XML)

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
7 REPLIES
Community Manager

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

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.
http://www.talendforge.org/forum/viewtopic.php?id=13711
Please try this solution and give us your feedback.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
One Star

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

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

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

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
One Star

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

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?