Which settings do I have to configure to use XML as input Parameter with Oracle using Spring's stored procedure

OK, so fixing this was a small dependency abomination, due to Oracle's byzantine library publishing habits.

The code changes as such are relatively minimal:

private class InsertXMLDataProcedure extends StoredProcedure {

    public InsertXMLDataProcedure(DataSource ds) {
        super(ds, PROCEDURE_NAME);
        declareParameter(new SqlParameter("COMPANYNO", Types.INTEGER));
        declareParameter(new SqlParameter("XMLDATA", OracleTypes.OPAQUE, "SYS.XMLTYPE"));
        declareParameter(new SqlParameter("ERRORNO", Types.INTEGER));
        declareParameter(new SqlParameter("ERRORDESC", Types.VARCHAR));
        compile();
    }

    public Map<String, Object> execute(int companyNumber, String xmlData, int errorNumber, String errorDescription) {
        return super.execute(companyNumber, new OracleXmlTypeValue(xmlData), errorNumber, errorDescription);
    }
}

It basically comes down to changing the paramter type to:

new SqlParameter("XMLDATA", OracleTypes.OPAQUE, "SYS.XMLTYPE")

and wrapping the xml into a OracleXmlTypeValue object.

Both of which are available in spring's spring-data-oracle extension. The maven dependency at the time of writing is as follows:

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-oracle</artifactId>
    <version>1.1.0.RELEASE</version>
</dependency>

Now that was the easy bit. You will also have to provide a set of jars to make this work. These are not in any maven repository, you'll have to install them locally or upload them to your local nexus.

The jars in question are:

• xdb6.jar
• xmlparserv2.jar

xdb6.jar is available from Oracle Technology Network (OTN) on the Oracle driver download page, located at http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html.

xmlparserv2.jar is much harder to find. If you're lucky, you'll find it in {ORACLE_HOME}/oracle/produce/{VERSION_NUMBER}/lib/xmlparserrv2.jar on your database machine. If not, like me, you'll find it packaged with Jdeveloper, which is obtainable here: http://www.oracle.com/technetwork/developer-tools/jdev/downloads/index.html - you should pick the Java Edition, the jar's in modules/oracle.xdk_12.1.3.

Do not try to download XDK directly, it's an old version. Using that library will give java.lang.NoClassDefFoundError: oracle/xml/binxml/BinXMLMetadataProvider. Oracle quietly changed the contents of xmlparserv2.jar whithout bumping the version; as I said byzantine habits.

N.B. all of this applies to Oracle 11 and higher.