4

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Karthick88it
  • 601
  • 2
  • 12
  • 28
  • Use [`OracleResultSet#getBinaryStream(columnIndex)`](https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBinaryStream(int))? – MT0 Jan 09 '19 at 09:25
  • I have tried already "InputStream is= orset.getBinaryStream("xmlrecord");" but getting error as "Invalid column type: getBinaryStream not implemented for class oracle.jdbc.driver.T4CNamedTypeAccessor" – Karthick88it Jan 09 '19 at 09:34
  • Have you tried it with a column index (`int` data type) argument rather than the column name (`string` data type)? – MT0 Jan 09 '19 at 09:43
  • Tried with "orset.getBinaryStream(1);" getting the same above error – Karthick88it Jan 09 '19 at 09:46
  • You're getting to the depths of detail where it would be helpful to know which Oracle driver you are using. Using Oracle's [`ojdbc` drivers](https://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html) the `ResultSet#getBinaryStream(int)` method works for me and if the driver you are using is different then it appears that the implementation in your driver does not support the same methods. – MT0 Jan 09 '19 at 09:55
  • Currently i am using ojdbc6 in my application. Please let me know which version you are using presently. – Karthick88it Jan 09 '19 at 09:57
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/186405/discussion-between-karthick-sambanghi-and-mt0). – Karthick88it Jan 09 '19 at 09:59

1 Answers1

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
}
Community
  • 1
  • 1
MT0
  • 143,790
  • 11
  • 59
  • 117