oracle11gr2xmltypewebmethods

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

I am trying to insert a XML request data into a table where the size of the XML message is more than 4KB and I am receiving the error: 'ORA-01461: can bind a LONG value only for insert into a LONG column'. I am receiving the XML request data as part of a transaction as a string data and due to the server used (webMethods Integration server 9.0) I have a limitation of converting the data into another format. I have tried the following methods:

  • insert into testtable values (xmltype())
  • insert into testtable values (xmltype().getClobVal())
  • insert into testtable values (TO_CLOB())

    • The table schema is as given below:

    desc testtable
Name    Null     Type      
------- -------- --------- 
XMLDATA NOT NULL XMLTYPE()

    The insert to the table should also happen within a short time as the operation is executed for every request message that is coming to the server. Please help.

    Solution

  • I modified the above code a bit and it is working now, but I am not really sure why:

    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;

    This is working for XML type data above 4KB as well. @nop77svk - thanks for your guidance.