Extracting CLOB from oracle table using Jdbc select

, ,
1

Hi,

I am facing problem retrieving CLOB from a table.
Source application is storing an XML as a CLOB in an table.
I have set an insert notification which triggers an JDBC select service.
This service has to get the CLOB as a string variable which will be the XML stored.
Once i receive the XML, the target integration logic will get called.

Problem : The JDBC select query results into an null string for the CLOB field.

Does anyone knows how to get the CLOB properly as a string?

We are using oracle thin driver.

regards,
Sumit

2

We had the same problem and resolved it using the OCI driver. Its the thick driver.

3

Hi,

Thanks for the response.
I have gone through the all the posts where OCI driver is mentioned

We used the same driver, but now the insert notification gives an error whenever we insert CLOB in the table on which insert notification is created. The error is as follows :


2006-03-02 12:50:29 GMT+05:30 [ART.0116.3502V1] Adapter Runtime (Notification): Execution begin for
Notification:VTLeadCreation.Notifications.Woe:WoeInsertNotify UUID:a02295c90487ef9d10900eccd00.
2006-03-02 12:50:29 GMT+05:30 [ADA.0001.0103V1] Begin local transaction.
2006-03-02 12:50:29 GMT+05:30 [ART.0116.3507V1] Adapter Runtime (Notification): useConnection Connec
tion:com.wm.adapter.wmjdbc.connection.JDBCConnection@228c70e0 for Notification:VTLeadCreation.Notifi
cations.Woe:WoeInsertNotify UUID:a02295c90487ef9d10900eccd00.
2006-03-02 12:50:29 GMT+05:30 [ADA.0001.0109V1] In notification callback "runCallBack"().
2006-03-02 12:50:29 GMT+05:30 [ART.0116.3508V1] Adapter Runtime (Notification): getConnection Connec
tion:com.wm.adapter.wmjdbc.connection.JDBCConnection@228c70e0 for Notification:VTLeadCreation.Notifi
cations.Woe:WoeInsertNotify UUID:a02295c90487ef9d10900eccd00.
2006-03-02 12:50:29 GMT+05:30 [ART.0116.3001E] Adapter Runtime (Notification): Notification error du
ring execution of VTLeadCreation.Notifications.Woe:WoeInsertNotify. Error: oracle/jdbc/driver/Oracle
ResultSet.getClob.
2006-03-02 12:50:29 GMT+05:30 [SCC.0121.0035E] resource delisted with TMFAIL flag, transaction marke
d for rollback. xid = CTP-VI0218A/1141281647591 rxid = [FormatId=45744, GlobalId=CTP-VI0218A/1141281
647591, BranchQual=1]
2006-03-02 12:50:29 GMT+05:30 [ADA.0001.0105V1] Rollback local transaction.
java.lang.AbstractMethodError: oracle/jdbc/driver/OracleResultSet.getClob
        at com.wm.adapter.wmjdbc.connection.JDBCType$20.getOutput(JDBCType.java:890)
        at com.wm.adapter.wmjdbc.services.FieldMap.setResultIData(FieldMap.java:169)
        at com.wm.adapter.wmjdbc.notifications.AutomaticNotification.publishData(AutomaticNotificati
on.java:307)
        at com.wm.adapter.wmjdbc.notifications.AutomaticNotification.runNotification(AutomaticNotifi
cation.java:265)
        at com.wm.adk.notification.WmPollingNotification.execute(WmPollingNotification.java:106)
        at com.wm.pkg.art.scheduler.NotificationData.run(NotificationData.java:291)
        at com.wm.util.CronJob.run(CronJob.java:477)2006-03-02 12:50:29 GMT+05:30 [SCC.0121.0050I] r
ollback-only flag is set.  rolling back transaction, xid = CTP-VI0218A/1141281647591
2006-03-02 12:50:29 GMT+05:30 [ART.0116.3507V1] Adapter Runtime (Notification): useConnection Connec
tion:null for Notification:VTLeadCreation.Notifications.Woe:WoeInsertNotify UUID:a02295c90487ef9d109
00eccd00.
 
        at com.wm.util.pool.PooledThread.run(PooledThread.java:105)
        at java.lang.Thread.run(Thread.java:512)
tion:com.wm.adapter.wmjdbc.connection.JDBCConnection@693670f3 for Notification:VTLeadCreation.Notifi
cations.Woe:WoeInsertNotify UUID:6a4635aaf56c5841109009f1aed.

Please let me know if anything is missing somewhere.

Thanks again.
regards,
Sumit

4

Hi,

I have solved the problem using java service.
The JDBC adapter service takes clob as “java.sql.Clob” type however the sql query return clob as “oracle.sql.CLOB”. Hence the JDBC select service is giving error for getClob method.

Instead of using jdbc adapter service, i have created a java service and used resultset.getObject to get the oracle.sql.CLOB object and type cast it to CLOB.

Hope it helps others.

regards,
Sumit

5

Interesting…
public class CLOB implements Clob
-signature of CLOB suggests that getClob should work.

6

Hi

Did u try using driverType = thin instead when using oci connection

Cheers
Jeevan

7

Hi,

I tried using driverType=oci but it did not work.
Then i did the java service using driverType=thin.

regards,
Sumit

8

Hi,

In continuation to my query, we are facing problem with polling notification now.
One of the field in our table is a clob field.
We have a insert notification on the same. The insert notification just sends a string “oracle.sql.CLOB”.
When tried to use java service to typecast it to “java.sql.Clob” and use getSubString() on it, it gave “java.lang.ClassCastException: java.lang.String” exception.

The same java service works fine when used in conjunction with jdbc select service instead of polling notification.

Let me know if anyone has come across such a situation.

regards,
Sumit