Error when persisting large XML into Oracle DB using WM Adapter for JDBC

Hi All,

We are using webmethods 9.9 and have a small problem with persisting XML into an Oracle database using the WmJDBC adapter

The DB table looks something like this

description VARCHAR2(40),
details XMLTYPE

When I create an adapter service,and click on the INSERT tab,and then the ‘Insert Row’ button I get the following

COLUMN COLUMN_TYPE JDBC_TYPE Input Field Type

DETAILS XMLTYPE SQLXML java.lang.String {Not Editable i.e no drop down}

I now run the service, pass a small xml {1000bytes}
and it works.

But greater than 4000 {which is our requirement}, i get the following error
ORA-01461: can bind a LONG value only for insert into a LONG column .

From a bit of reading I understand that this occurs when the ‘input’ passed to DB is >4000 bytes. My question is what seems to be the problem? java.lang.String seems fine to handle that size, I also know that XML type is backed internally by a CLOB.

Note:
I tried something similar using PL/SQL

details_msg varchar2 := ‘the large xml went here’

then did a

insert into table values (‘xyz’,XMLTYPE.CREATEXML(details_msg ))

and this failed with the same error
When i changed the object type of details_msg to CLOB it worked !

Can you please provide some help with how to approach this in Webmethods?[u]

If I rememeber correctly, you can make use of longVarchar. Did you use ?

Thanks,

Or it might be nvarchar. Please check and make use of that.

Thanks,

As per DBA consultation change column datatype to CLOB.

there might be another way, but here what worked for me:

using a CLOB field in the database (Oracle 11g)

in the IS:
your JDBC insert adapter field need to have:
Column type=CLOB,
JDBC type = CLOB
input field type = java.io.Reader

then in your flow service: use pub.io:stringToReader to convert your XML before sending it to the adapter.

I hope that helps