Execute Service Adapter template Exception to insert CLOB

I am trying to use the execute service adapter template which executes a java service using the connection parameter from pipeline to execute a function on a Oracle8i database.

java.sql.Connection conn =
(java.sql.Connection)IDataUtil.get(pipeline.getCursor(), com.wm.adapter.wmjdbc.services.ExecuteService.PIPELINE_CONNECTION);

I am able to execute simple procedures and functions which involes only varchar and number types but the problem is i have to insert a clob data to my procedure call which fails with below error.

[ADA.1.345] Exception occured when executing Service “testService”
Exception :$Proxy6

I could figure out its failing for temporary clob creation.
tempClob = CLOB.createTemporary( conn, false, CLOB.DURATION_SESSION );

Exception
deep copy problems: com.wm.app.b2b.server.ServiceException: Exception :$Proxy6
Before Clob Creation - Connection - oracle.jdbc.driver.T4CConnection@2ad006

Any one has seen this error before?

Regards,
Monish

Hi

I’m trying to select the fields from Oracle database using Dynamic sql Adapter…
In that one of the column is of CLOB datatype.

I’m getting following error when I’m trying to select the column having clob datatype, rest all columns are normally selected i.e, Varchar2…

java.io.WriteAbortedException: writing aborted; java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection

java.io.WriteAbortedException: writing aborted; java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection
at com.wm.util.coder.IDataBinCoder.doJavaSerializationDecode(IDataBinCoder.java:1215)
at com.wm.util.coder.IDataBinCoder._decode(IDataBinCoder.java:1179)
at com.wm.util.coder.IDataBinCoder._decode(IDataBinCoder.java:802)
at com.wm.util.coder.IDataBinCoder._decode(IDataBinCoder.java:972)
at com.wm.util.coder.IDataBinCoder._decode(IDataBinCoder.java:802)
at com.wm.util.coder.IDataBinCoder._decode(IDataBinCoder.java:802)
at com.wm.util.coder.IDataBinCoder._decode(IDataBinCoder.java:802)
at com.wm.util.coder.IDataBinCoder.decode(IDataBinCoder.java:298)
at com.wm.driver.comm.b2b.http.SessionHTTPLink.transportMsg(SessionHTTPLink.java:89)
at com.wm.driver.comm.b2b.http.HTTPLink$HTTPSendReceive.run(HTTPLink.java:375)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1184)
at java.io.ObjectOutputStream.defaultWriteFields(ObjectOutputStream.java:1548)
at java.io.ObjectOutputStream.writeSerialData(ObjectOutputStream.java:1509)
at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1432)
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1178)
at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:348)
at com.wm.util.coder.IDataBinCoder.doJavaSerializationEncode(IDataBinCoder.java:636)
at com.wm.util.coder.IDataBinCoder._encode(IDataBinCoder.java:616)
at com.wm.util.coder.IDataBinCoder._encode(IDataBinCoder.java:465)
at com.wm.util.coder.IDataBinCoder._encode(IDataBinCoder.java:561)
at com.wm.util.coder.IDataBinCoder._encode(IDataBinCoder.java:465)
at com.wm.util.coder.IDataBinCoder._encode(IDataBinCoder.java:465)
at com.wm.util.coder.IDataBinCoder._encode(IDataBinCoder.java:465)
at com.wm.util.coder.IDataBinCoder.encode(IDataBinCoder.java:272)
at com.wm.app.b2b.server.ServerHTTPLink.send(ServerHTTPLink.java:69)
at com.wm.app.b2b.server.comm.DefaultServerRequestHandler.handleMessage(DefaultServerRequestHandler.java:143)
at com.wm.app.b2b.server.HTTPMessageHandler.process(HTTPMessageHandler.java:156)
at com.wm.app.b2b.server.HTTPDispatch.handleRequest(HTTPDispatch.java:178)
at com.wm.app.b2b.server.Dispatch.run(Dispatch.java:384)
at com.wm.util.pool.PooledThread.run(PooledThread.java:127)

Can any one of help me with this… The webMethods version I’m using is 9.8 and OJDBC6 is the Jar I’m using.

Many Thanks in advance :slight_smile:

Regards
Hari

Please try this resolution and see if this resolves:

[b]Resolution:

All objects which exist in the pipeline must be encoded into a binary format (i.e. serialized).
To resolve the issue, change the “Output Field Type” of the adapter service from java.sql.Blob to “byte array”. The resulting output filed will then be passed to a flow service as a byte array.[/b]

HTH,
RMG

Hari – Did you follow instructions given by RMG. It’s really a nice topic. Please share your finding after execution.

Thanks,

Yes By changing the type in output filed, I’m able to select the CLOB datatype columns. Many Thanks RMG.

Regards
Hari

How would you change the output field type for the columns in a dynamic sql, where you are getting clob as a dynamic output field?

Hi,

when defining the service you can change the output type of each field, which is visible in the output tab of the service.

For CLOB columns you should use java.lang.String, as this ease the processing of the data in later steps of your flow.

Regards,
Holger

Hi Holger, I see what you are saying but in my case, the sql is totally dynamic with dynamic table and column names. I dont know, which table might have which column as Clob so I cannot really define the exact output with the type.