topic Re: [resolved] Oracle dynamic row doesn't work with clob in Talend Studio

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

Anonymous — Fri, 13 Nov 2015 15:37:22 GMT

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

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

Anonymous — Fri, 13 Nov 2015 15:55:05 GMT

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.

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

Anonymous — Tue, 17 Nov 2015 08:52:33 GMT

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

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

Anonymous — Tue, 17 Nov 2015 09:38:13 GMT

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.

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

Anonymous — Thu, 19 Nov 2015 09:00:31 GMT

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);
  }
  }
 }
}

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

Anonymous — Thu, 19 Nov 2015 09:14:28 GMT

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.

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

Anonymous — Thu, 19 Nov 2015 09:32:37 GMT

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.

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

_AnonymousUser — Mon, 12 Sep 2016 15:58:36 GMT

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.