Insert XML with more than 4000 characters into a Oracle XMLTYPE column

Question

I have an oracle table with a column from type "SYS.XMLTYPE" and a storage procudure which is doing the insert:

(Short version):

PROCEDURE InsertXML 
(
     pXMLData IN LONG 
)
IS
BEGIN

    INSERT INTO MY_TABLE (XML_DATA) VALUES(pXMLData);

END InsertXML;

I call this sp from my C# code with type "OracleType.LongVarChar".

Now the problem: If the xml has less than 4000 characters everything is working fine, but by using a xml with more than 4000 characters I get the following error:

ORA-20000: ORA-01461: can bind a LONG value only for insert into a LONG column

How can I handle this? Thx 4 answers

Answer 1

Check the Oracle docs about XMLType

Also, I believe the datatype should be a CLOB (Character Large Object).

Answer 2

You need to convert xml string for more than 4000 charcaters into SQLXML type first.

Environment: jpa 2.1.0, eclipselink 2.5.2, oracle db 11gr2

SQL:

CREATE TABLE "XMLTEST"
( "ID" NUMBER(10,0) NOT NULL ENABLE, 
  "DESCRIPTION" VARCHAR2(50 CHAR) NOT NULL ENABLE, 
  "XML_TXT" "XMLTYPE" NOT NULL ENABLE
);

INSERT INTO XMLTEST (ID, DESCRIPTION, XML_TXT) VALUES (101, 'XML DATA', '<data>TEST</data>');
COMMIT;

DROP TABLE "XMLTEST";

Java Code

String sql = "INSERT INTO XMLTEST (ID, DESCRIPTION, XML_TXT) VALUES (?, ?, ?)";
String xmlDataStr = "<data>test...</data>"; // a long xml string with length > 4000 characters
Connection con = getEntityManager().unwrap(Connection.class);
SQLXML sqlXml = con.createSQLXML();
sqlXml.setString(xmlDataStr);

Java code - use PreparedStatement

PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setLong(1, 201);
pstmt.setLong(2, "Long XML Data");
pstmt.setSQLXML(3, sqlXml);
pstmt.execute();

Java code - use native query instead of PreparedStatement

Query query = getEntityManager().createNativeQuery(sql);
query.setParameter(1, 301);
query.setParameter(2, "Long XML Data");
query.setParameter(3, sqlXml);
query.executeUpdate();

Answer 3

According to Stuart Campbell

• I inserted a XMLTYPE into a CLOB
• but the statement itself had to be wrapped in a transaction.

Without transaction, it did not work....