JDBC Adapter Service and large CLOBs

, ,
1

Hi all,

When creating an JDBC insert service to populate a CLOB column in a database, I receive the following error message when attempting to insert a string more than 4000 characters in length:

ORA-01400: cannot insert NULL into (“schema”, “table”, “clob_column”)

If I repeat the same test with a string less than 4000 characters in length the insert is succesful. The input field type is set to String and the JDBC type is CLOB.

I presume this is a webMethods bug. I know that the maximum length for a column is 4000 characters in Oracle, but the same insert works in a Java service. Which is the obvious workaround.

My question is whether this is a known bug or if I doing something wrong?

Thanks.

2

Not a webMethods bug but a limitation of the Oracle driver you are using I suspect. Which version are you using?

Oracle 8.1.x and 9i have limitations on the thin type 4 drivers fro blob and clob size. The oracle 10g driver removed the limitation. You have a lot choices for getting around this though.

You can…Use the oracle OCI driver instead of the thin type 4 driver. You can upgrade your oracle driver to 10g. You can write a java service and stream in the clob via a stored procedure.

markg
http://darth.homelinux.net

3

When you HAVE to insert a null into a CLOB column, instead of NULL use EMPTY_CLOB(). The SQL statement goes like this (I think you can modify the adapter service to do something similar).

  INSERT INTO SAMPLE_TABLE 
              (SMAPLE_id, 
               SAMPLE_clob) 
  VALUES      (1234, 
               EMPTY_CLOB());
4

I am using Oracle 9i. I will use a Java service to work around the problem. I was just wondering if it was possible with a JDBC adapter service.

Thanks for your replies.

5

Hi qwerty,

Try using JDBC Oracle OCI driver for CLOBs greater than 4k. While configuring jdbc connection with OCI driver , give the driverType has oci8 for Oracle 8i and oci for Oracle 9i.

Regards,
Venkat.

6

Just to confirm, switching from the “thin” driver to “oci” worked for me.

Thanks Venkat.

7

querty ,

   noproblem this will works
8

Is there a way to access the JDBC adapter connection pool defined in wM from your Java code ?
The idea is to save the work of creating and managing a JDBC pool and use what wM already (if) offers.
Will really appreciate some help on this.
SPG

9

Hi shubhro,

I don’t think this is possible. It is possible to define DB connection parameters in the WmDB package, and then access these from your Java code.

10

I am trying to fetch data from a table which has a CLOB column. I can fetch the data from all columns correctly but the data from CLOB column comes as NULL , though I have data in it.

Pls let me know if you have any suggestions.

Thanks
Vij

11

What is the jdbc field type in your select?Is it object type?

12

Hi,

Please refer to my post:
[url]wmusers.com

regards,
Sumit