Error in inserting an XMLTYPE column data into a Oracle 11g database more than 4KB

, ,
1

I am trying to insert a XML request data into a table where the size of the XML message is more than 4KB and receiving the error: ‘ORA-01461: can bind a LONG value only for insert into a LONG column’. I tried to install the OCI drivers and configured the adapter to accept the XMLtype input. But the input value in the adapter is resolving to either java.lang.String, java.lang.Object, java.sql.Array or java.sql.Struct. I have imported the xdb6.jar file that lets us work with the java.sql.SQLXML but I am not sure how to link it to the connection or the adapter. The table has been designed with an XMLTYPE column and its schema cannot be changed. Please help.

2

Try using clob data type.

3

Thanks for your reply Mahesh - Unfortunately the table design has already been fixed and it is an SOR for multiple applications. Is there any way to work with the XMLTYPE Column?

4

I see your on wM 9.6 and Java 1.7? Is this right?

See if this helps. If not we will think of other options and lets see what others comment.

or

5

Also try using java.lang.String or SetAsString data type?

6

Which Fixes for IS and JDBC-Adapter are installed?
Which driver are you using? Hopefully latest ojdbc6.jar from Oracle11gR2 (11.2.0.4).

Regards,
Holger

7

Thanks for your reply Holger.
Component Stack:

  1. webMethods Integration Server - 9.0
  2. JDBC Adapter - 6.5
  3. Oracle 11g R2 Enterprise
  4. Java Version - 1.7.0_25
  5. JDBC driver version - 11.2.0.4

Please let me know if you need any further information.

8

Rohit,

Please answer these:

Which Fixes for IS and JDBC-Adapter are installed?

Which driver/ojdbc jar version are you using placed in the IS/lib/jars?

HTH,
RMG

9

I haven’t installed any of the fixes either for the IS or the JDBC adapter. I have placed the driver version : 11.2.0.4 in the IS/lib/jars folder.

Thanks,
Rohit

10

What driver/jar file you put ojdbc4 or ojdbc6.jar?

HTH,
RMG

11

@rmg - ojdbc6.jar that is the one I imported.

I was able to come up with a work around for this by using an anonymous PL/SQL block. When used alongside a Dynamic SQL adapter it works.

declare
    l_long          long;
    l_clob          clob;
begin
    l_long := :1;
    l_clob := TO_CLOB(l_long);
    insert into testtable values (xmltype(l_clob));
    dbms_lob.freetemporary(l_clob);
end;

I was still wondering if this can be done by defining an Insert SQL Adapter.

12

OK great work around…but not that I know of from standard Insert template.

HTH,
RMG