Saving large XMLTypes in Oracle databases

If you try to save a large XMLType that is over 4k characters long, you'll get this error:

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

Oracle database has troubles binding large strings. One solution is to use a temporary clob to store the XML data, and bind that to the SQL-statement.

Connection con = ...
String xml = "<test>test</test>";
oracle.sql.CLOB clob = null;

clob = CLOB.createTemporary(con, true,
    CLOB.DURATION_SESSION);
clob.open(CLOB.MODE_READWRITE);
clob.setString(1, xml);
clob.close();
clob.freeTemporary();

PrepareStatement s = con.prepareStatement(
  "INSERT INTO table (xml) VALUES(XMLType(?))"
);

s.setObject(1, clob);

Using this method, you can store large XML-documents as XMLType in the Oracle database. But there is still a limitation with the XMLType. The XMLType cannot store any text-nodes and attributes values larger than 64k. Oracle suggests to use a XMLType CLOB-storage column to store really large XML-documents. But then, we cannot use native XML-parsing functions, which defeats the purpose of the XMLType column...

In newer versions of Oracle XML DB, this limit has been lifted.

References:

• 64k size limit for XML node
  
• Store large XML in Oracle XML DB
  
• What's new in Oracle XML DB?