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

, ,
1

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]

2

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

Thanks,

3

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

Thanks,

4

As per DBA consultation change column datatype to CLOB.

5

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