0 Replies Latest reply: Jan 19, 2015 4:24 AM by bgillis RSS

    SQLInput.readSQLXML() returning oracle.sql.OPAQUE object instead of java.sql.SQLXML

    bgillis

      Manifest-Version: 1.0

      Ant-Version: Apache Ant 1.6.5

      Created-By: 1.5.0_30-b03 (Sun Microsystems Inc.)

      Implementation-Vendor: Oracle Corporation

      Implementation-Title: JDBC

      Implementation-Version: 11.2.0.3.0

       

      Oracle DB: 10.2.0.1

      Oracle JDK: 1.6.0_45

      OS: Windows Server 2008 SP2 64bit

       

      Hi guys,

       

      I'm currently trying to call a PL/SQL stored procedure (SELECT_XMLDTA_TYP) returning a custom Oracle type (XMLDTA_TYP) containing an XMLTYPE attribute (XMLDTA_VAL).

       

      Oracle type

       

      CREATE OR REPLACE TYPE XMLDTA_TYP AS OBJECT

      ( XMLDTA_ID VARCHAR2(12),

        XMLDTA_VAL XMLTYPE

      )

       

      Oracle stored procedure

       

      CREATE OR REPLACE PROCEDURE SELECT_XMLDTA_TYP(p_xmldta_id IN XMLDTA.XMLDTA_ID%TYPE,

                                        p_xmldta_typ OUT XMLDTA_TYP)

      IS

      BEGIN

          -- Init Type

          p_xmldta_typ := XMLDTA_TYP(NULL, NULL);

      SELECT

          t.xmldta_id,

          t.xmldta_val

      INTO

          p_xmldta_typ.XMLDTA_ID,

          p_xmldta_typ.XMLDTA_VAL

      FROM

          bgi_xmldta t

      WHERE

          xmldta_id = p_xmldta_id;

      END;

       

      Oracle table

       

      CREATE TABLE

          XMLDTA

          (

              XMLDTA_ID VARCHAR2(12) NOT NULL,

              XMLDTA_VAL DLBEAI.XMLTYPE NOT NULL

          );

       

      To do so I provide to the Connection a map containing the mapping between my custom Oracle type and the corresponding Java class implementing java.sql.SQLData.

       

      Map<String, Class<?>> typeMap = con.getTypeMap();

      typeMap.put(XmldtaOracleType.ORACLE_OBJECT_NAME,

           XmldtaOracleType.class);

       

      XmldtaOracleType is the class implementing java.sql.SQLData interface.

       

      In particular

       

          public void readSQL(SQLInput stream, String typeName) throws SQLException {

           setId(stream.readString());

           setValue(stream.readSQLXML());

         }

       

      However, I've got a SQLException when the CallableStatement is executed.

       

      Caused by: java.sql.SQLException: Invalid column type

          at oracle.sql.OracleJdbc2SQLInput.readSQLXML(OracleJdbc2SQLInput.java:1115)

          at com.mainsys.test.dao.oracle.XmldtaOracleType.readSQL(XmldtaOracleType.java:59)

          at oracle.sql.STRUCT.toClass(STRUCT.java:560)

          at oracle.sql.STRUCT.toJdbc(STRUCT.java:506)

       

      As a matter of fact, the next attribute to be read from the stream variable is not a java.sql.SQLXML but a oracle.sql.OPAQUE.

      That's weird because "SYS.XMLTYPE" should be automatically mapped to java.sql.SQLXML when using a JDBC 4 driver.


      I have currently in my classpath the following jar files:

      • ojdbc6-11.2.0.3.jar,
      • orai18n-11.2.0.3.jar,
      • xdb6-11.2.0.3.jar,
      • xmlparserv2-11.2.0.3.jar.

       

      So readSQLXML() should normally return a java.sql.SQLXML instead of a oracle.sql.OPAQUE.


      I have currently found a workaround to solve this issue by replacing the line


      setValue(stream.readSQLXML());

       

      by the following lines

       

      OPAQUE opaque = (OPAQUE) stream.readObject();

      setValue(XMLType.createXML(opaque));

       

      However I do not really want to depend on any specific class of Oracle JDBC driver.

       

      Moreover, if I switch to JDK 7 and use the latest available jar files for the JDBC driver,

      • ojdbc7-12.1.0.1.jar,
      • orai18n-12.1.0.1.jar,
      • xdb6-12.1.0.1.jar,
      • xmlparserv2-12.1.0.1.jar,

      everything is working as expected without changing any line of code.

       

      So, am I missing anything or is the Oracle JDBC driver 11.2.0.3 not really JDBC 4 compliant ?!?!

       

      Thanks in advance for your help,

       

      Bertrand