Uploading a large XML file into an XMLType column

The XML File is read as a string and is loaded into the XMLType column. The setString() statement was used to load the string (XMLType data) into the SQL prepared statement. As the string that had to be loaded was large (bigger than 4kb accepted by setString()) the statement was in error.

However there was a procedure suggested by ORACLE, where the setObject() is used instead of setString(). In which case, a CLOB is pushed into the prepared statement.
1. The getCLOB() method writes the contents of the string into a CLOB using ‘write’ method of the ‘Writer’ class and returns the CLOB.

Code Snippet:

CLOB tempClob = null;
// Open the CLOB in readwrite mode to enable writing
tempClob.open(CLOB.MODE_READWRITE);

// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();

// Write the data into the temporary CLOB
tempClobWriter.write(xmlData);

// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();


2. The CLOB is then loaded into the prepared statement using setObject().

Code Snippet:

PreparedStatement insertsql = Con.prepareStatement(

"INSERT INTO PO_XML_TAB2 VALUES(200,

XMLType(?))");

insertsql.setObject(1, clob);


References