Retrieving Oracle Xmltype Stored As Binary Xml From A Resultset In Java

I outlined a solution to writing/reading to an Oracle XMLType column for Oracle 11g (specifically 11.2.0.1) if the XMLType column is stored as a CLOB, which is the default for 11.2

Solution 1:

Found the catch, and it's not related to the code.

The proper nullSafeGet in the Hibernate UserType, as noted in the referenced answer is:

publicvoid nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {

    if (logger.isTraceEnabled()) {
        logger.trace("  nullSafeSet: " + value + ", ps: " + st + ", index: " + index);
    }
    try {
        XMLType xmlType = null;
        if (value != null) {
            xmlType = XMLType.createXML(getOracleConnection(st.getConnection()), (String)value);
        }
        st.setObject(index, xmlType);
    } catch (Exception e) {
        thrownew SQLException("Could not convert String to XML for storage: " + (String)value);
    }
}

PROBLEM: when using a SECUREFILE BINARY XML column (not CLOB) you must use the most recent (11.2.0.2+) distribution of the xdb*.jar, which in this case is xdb6.jar (~257kb). The earlier xdb*.jar (~136kb for 10.x) will still function, without tossing any exceptions even when incorrectly decoding BINARY XML.

TL;DR: Download xdb6.jar (~257kb) from the Oracle 11gR2 (11.2.0.3) JDBC drivers page. Older xdb jars fail silently and will make you sad.