[resolved] Oracle dynamic row doesn't work with clob

Four Stars

[resolved] Oracle dynamic row doesn't work with clob

If I use dynamic_row on an input oracle table containing clobs and output to a file, the output just puts an object reference out for the clob fields such as oracle.sql.CLOB@79885c3a.
Is this a bug or am I doing something wrong. vn 5.2.2 & 5.6.2
Community Manager

Re: [resolved] Oracle dynamic row doesn't work with clob

It's been a while since I have hit this problem and thought Talend had made it easier to work with. Basically a Clob is not necessarily a String. In Java it has its own class (java.sql.Clob). If you want to work with Clobs you have to manipulate the Class yourself (unless things have changed that I am not aware of - Anyone?).
Take a look at this (http://wiki.scn.sap.com/wiki/display/Snippets/Converting+from+clob+to+string). You can write a Java routine to do this and then use it in a tMap or something. The Oracle component will return the Clob as an Object so you will need to cast it to a Clob before you can use it as a Clob.
Four Stars

Re: [resolved] Oracle dynamic row doesn't work with clob

Not sure how this helps me to compare values in a clob with dynamic row if Oracle doesn't retrieve the real value.
Community Manager

Re: [resolved] Oracle dynamic row doesn't work with clob

A Clob is a complex data type and does not necessarily hold a String. Clobs can hold things like images, for example. So no, this is not a bug. The information I posted is what you need.
Four Stars

Re: [resolved] Oracle dynamic row doesn't work with clob

It works ok when there is 1 schema connection but fails if there are 2 different schemas. How do I point the java.sql.clob to a particular connection?

Exception in component tJavaFlex_2
java.sql.SQLException: Closed Connection
 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.sql.CLOB.getDBAccess(CLOB.java:1083)
 at oracle.sql.CLOB.length(CLOB.java:145)
 at ledmdev.match_target_to_ledr_0_1.Match_target_to_LEDR.tOracleInput_2Process(Match_target_to_LEDR.java:2679)
 at ledmdev.match_target_to_ledr_0_1.Match_target_to_LEDR.tOracleConnection_1Process(Match_target_to_LEDR.java:1291)
 at ledmdev.match_target_to_ledr_0_1.Match_target_to_LEDR.runJobInTOS(Match_target_to_LEDR.java:3685)
 at ledmdev.match_target_to_ledr_0_1.Match_target_to_LEDR.main(Match_target_to_LEDR.java:3432)


// start part of your Java code
java.util.Map<String, Object> viewMap = new java.util.HashMap<String, Object>();
java.util.Map<String, Object> tableMap = new java.util.HashMap<String, Object>();     
int numColumns;
String tableColumnName;
Object tableColumnValue;
String viewColumnName;
Object viewColumnValue=null;
String colNames;
java.sql.Clob newClobView;
java.sql.Clob newClobTable;
//Main part of java code
numColumns = view.remainder.getColumnCount();
for (int i = 0; i < numColumns; i++)
{
   viewColumnName = view.remainder.getColumnMetadata(i).getDbName();
   viewColumnValue = view.remainder.getColumnValue(i);
 if ( view.remainder.getColumnMetadata(i).getType().equalsIgnoreCase("id_Object")) {
         /* convert old and new values to CLOB type */
         newClobView = ((java.sql.Clob) view.remainder.getColumnValue(i));
   //System.out.println("viewColumnValue " + viewColumnValue);         
         if (newClobView != null) {
         /* use getSubString method to easily convert to String */
         viewColumnValue = newClobView.getSubString(1L,(int) newClobView.length());
         System.out.println("viewColumnValue " + viewColumnValue);
         }
        }
      if (viewColumnValue == null) {
   viewColumnValue = "";
   }
 viewMap.put(viewColumnName, viewColumnValue);
}
numColumns = table.remainder.getColumnCount();
for (int i = 0; i < numColumns; i++)
{
 tableColumnName  = table.remainder.getColumnMetadata(i).getDbName();
 tableColumnValue = table.remainder.getColumnValue(i);
 if ( table.remainder.getColumnMetadata(i).getType().equalsIgnoreCase("id_Object")) {
         /* convert old and new values to CLOB type */
         newClobTable = ((java.sql.Clob) table.remainder.getColumnValue(i));
         
         if (newClobTable != null) {
         /* use getSubString method to easily convert to String */
         tableColumnValue = newClobTable.getSubString(1L,(int) newClobTable.length());
         }
        }
    if (tableColumnValue == null) {
  tableColumnValue = "";
 }
  tableMap.put(tableColumnName, tableColumnValue);
}
Set<String> keys = tableMap.keySet();
for (String key : keys) {
 tableColumnValue = tableMap.get(key);
 viewColumnValue = viewMap.get(key);
 if(viewColumnValue != null) {
 //System.out.println("key=: " + key + " tablevalue = " + tableColumnValue + " viewvalue = " + viewColumnValue);
 if (! tableColumnValue.equals(viewColumnValue))
 {
  if  (! tableColumnValue.toString().replaceAll("^0+","").equals(viewColumnValue) ) {
  System.out.println("Event_id = : " + out1.EVENT_ID + " column =: " + key + " tablevalue = " + tableColumnValue + " viewvalue = " + viewColumnValue);
  }
  }
 }
}
Community Manager

Re: [resolved] Oracle dynamic row doesn't work with clob

If you look at the "Code" tab for your Job and search for something like this.....
globalMap.get("conn_

....you will find how Talend uses your connection objects. For Oracle, the code will look something like below...
java.sql.Connection conn_tOracleInput_1 = null;
conn_tOracleInput_1 = (java.sql.Connection) globalMap.get("conn_tOracleConnection_1");

Simply retrieve the relevant connection from the globalMap in the same way.
Four Stars

Re: [resolved] Oracle dynamic row doesn't work with clob

Sorry for being thick but I'm not a java developer. How do I apply the connection info to the loop retrieving the clob?
OK I think I've sorted it out. Thanks for our help.
One Star

Re: [resolved] Oracle dynamic row doesn't work with clob

Hi ,
Were you able to fix this. i need a solution for the same issue.
Please post your answer here. I am getting data in the object format and i am facing issue when trying to load that clob data into Greenplum.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog