Is there an function to retrieve the binary XML from oracle result set

Question

I am trying to extract the data from XMLTYPE COLUMN "ATTRIBUTE_XML2" STORE AS SECUREFILE BINARY XML from an Oracle 12C database.

I am using this select query in my code:

select xmlserialize(document a.xmlrecord as clob) as xmlrecord from tablename

ResultSet rset = stmt.executeQuery();

OracleResultSet orset = (OracleResultSet) rset;
while (orset.next()) {
oracle.sql.CLOB xmlrecord = (oracle.sql.CLOB) orset.getClob(1);
Reader reader = new BufferedReader(xmlrecord.getCharacterStream()); 
}

Here "orset.getClob" is taking more memory in oracle DB and we are getting out of process memory in the oracle database. Currently we have the XML type storage as CLOB and business is interested to change it to BINARY XML.

Is there any option for retrieving the binary XML from the oracle result set?

Please note that i have tried "orset.getClob" which results in memory error, since it is changing the binary XML to clob.

Also tried with " XMLType xml = (XMLType) orset.getObject(1);" this is working fine, but it is taking 27 minutes for fetching 1 million XML records.

Whereas the same 1 million completed in 5 minutes if the table type storage is CLOB instead of BINARY XML.

Is there any other option for retrieving the BINARY XML ?

Answer 1

The Oracle documentation for Using JDBC to Access XML Documents in Oracle XML DB states that:

You can select XMLType data using JDBC in any of these ways:

• Use SQL/XML function XMLSerialize in SQL, and obtain the result as an oracle.sql.CLOB, java.lang.String or oracle.sql.BLOB in Java. The Java snippet in Example 13-2 illustrates this.
• Call method getObject() in the PreparedStatement to obtain the whole XMLType instance. The return value of this method is of type oracle.xdb.XMLType. Then you can use Java functions on class XMLType to access the data. Example 13-3 shows how to do this.

So you should be able to use XMLSERIALIZE( DOCUMENT your_binary_xml_column AS BLOB ) in SQL and then use OracleResultSet#getBLOB(int) to get the binary data.

Paraphrasing Oracle's Example 13-2 to cast to a BLOB instead of a CLOB:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@", "QUINE", "CURRY");
OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement(
     "SELECT XMLSerialize(DOCUMENT e.poDoc AS BLOB) poDoc FROM po_xml_tab e");
ResultSet rset = stmt.executeQuery();
OracleResultSet orset = (OracleResultSet) rset;
while(orset.next())
{
  // the first argument is a BLOB
  oracle.sql.BLOB clb = orset.getBLOB(1);
  // now use the BLOB inside the program
}