10 Replies Latest reply on Dec 27, 2012 9:13 PM by 941189

    SQL Xml issue in ojdbc 6 (


      I have a SYS.XMLTYPE column in one of my tables and trying to use hibernate to insert and retrieve from the same..
      A stand alone test code inserts and retrieves successfully.. when I use

      SQLXML sqlXML = preparedStmt.getConnection().createSQLXML(); // while inserting

      SQLXML sqlXML = rs.getSQLXML(names [0]); // while retrieving..

      But when the same is deployed in a server jboss as 7.1 / websphere 7.. I am able to insert properly but retrieval is giving me a Null pointer both in jboss and websphere..

      Caused by: java.lang.NullPointerException
      at oracle.jdbc.driver.NamedTypeAccessor.getOracleObject(NamedTypeAccessor.java:678)
      at oracle.jdbc.driver.NamedTypeAccessor.getSQLXML(NamedTypeAccessor.java:992)
      at oracle.jdbc.driver.OracleResultSetImpl.getSQLXML(OracleResultSetImpl.java:3028)
      at oracle.jdbc.driver.OracleResultSet.getSQLXML(OracleResultSet.java:1299)

      Can somebody help!!...

        • 1. Re: SQL Xml issue in ojdbc 6 (
          You still have to prepareStatement
          PreparedStatement st = c.prepareStatement("SELECT ID, SALARY FROM EMP ");
          ResultSet rs = st.executeQuery();
          while (rs.next())
          SQLXML article = rs.getSQLXML("SALARY");
          // Do something...
          • 2. Re: SQL Xml issue in ojdbc 6 (
            Sorry if my post was not clear... Hibernate would have prepared the statement and executed, I am just over riding the part where i want the xml to be converted as byte array...

            public byte[] nullSafeGet(final ResultSet rs, final String[] names, final Object owner)
                           throws HibernateException, SQLException

                      byte[] xml = null;
                      String xmlString = null;
                           SQLXML sqlXML = rs.getSQLXML(names [0]); // this is where i am getting the probelm if i run it in server...
                           if (null != sqlXML)
                                xmlString = sqlXML.getString();
                                xml = xmlString.getBytes();
                      catch (final HibernateException hibernateException)

            • 3. Re: SQL Xml issue in ojdbc 6 (

              Is there a solution to this issue ?

              I have similar pb with this code :

              PL/SQL :
              procedure P_FIND_xxx_XML(
              pa_1 t_table.v_field1%type,
              pa_2 t_table.v_field2_version%type,
              pa_3 t_table.v_field3%type,
              pa_4 t_table.v_field4%type,
              pa_5 t_table.n_field5%type,
              pa_6 t_table.n_field6%type,
              pa_7 OUT SYS_REFCURSOR


              Connection tmpConnection = null;
              CallableStatement tmpStatement = null;
              ResultSet tmpResultSet = null;
              SQLXML tmpSqlXml = null;

              tmpConnection = getConnection();
              tmpStatement = tmpConnection.prepareCall("{call pa_xxx.p_find_xxx_xml(?,?,?,?,?,?,?)}");
              tmpStatement.setString(1, p1);
              tmpStatement.setString(2, p2);
              tmpStatement.setString(3, p3);
              tmpStatement.setString(4, p4);
              tmpStatement.setLong(5, p5);
              tmpStatement.setLong(6, p6);
              tmpStatement.registerOutParameter(7, OracleTypes.CURSOR);


              tmpResultSet = ((OracleCallableStatement)tmpStatement).getCursor(7);


              tmpPKId = (tmpResultSet).getLong(1);
              if(tmpPKId != null)
              getLogger().info("performHistory --> " + "tmpPKId <" + tmpPKId + ">");
              tmpSqlXml = (tmpResultSet).getSQLXML(2);
              if(tmpSqlXml != null)
              getLogger().info("performHistory --> " + "tmpSqlXml <NOT NULL OBJECT>");
              getLogger().info("performHistory --> " + "tmpSqlXml <NULL OBJECT>");

              the code tmpPKId = (tmpResultSet).getLong(1); is OK and i have the value
              The (tmpResultSet).getSQLXML(2) throw an exception :
              at oracle.jdbc.driver.NamedTypeAccessor.getOracleObject(NamedTypeAccessor.java:302)
              at oracle.jdbc.driver.NamedTypeAccessor.getSQLXML(NamedTypeAccessor.java:413)
              at oracle.jdbc.driver.OracleResultSetImpl.getSQLXML(OracleResultSetImpl.java:1251)

              Any idea ?
              • 4. Re: SQL Xml issue in ojdbc 6 (
                I'm am having exact the same problem Jude mentioned.

                I tried a batch program accessing Oracle XMLType and everything went allright.
                But when writing an UserType for Hibernate 4, it crashes when I try to read the SQLXML field.
                Code from HibernateUserType:
                XMLType xml = (XMLType) rs.getSQLXML(names[0]);
                That one fails (accessing it via JPA on a JBoss 7).

                This code works fine from a batch program:
                xmls.add((XMLType) rs.getSQLXML(1));
                I can't see a big difference. Help would be appreciated.
                • 5. Re: SQL Xml issue in ojdbc 6 (
                  Well this is what i did.. down graded the oracle driver jar and the used older mechanisim for retrieving the xml type coumn.. as a clob..
                  • 6. Re: SQL Xml issue in ojdbc 6 (
                    That' s what i did also, when using a clob instead of a xmltype, it was working but the oracle administrator saw some strange oracle log errors that occured randomly :

                    Message=Internal error (ORA-600[qmxrsr_readcbk:1]) detected in /alert/log.xml at time/line number: Wed Mar 21 14:31:07 2012/138809.
                    Metric=Generic Internal Error
                    Metric value=Errors in file /trace/ora_19572.trc (incident=418523):~ORA-00600: internal error code, arguments: [qmxrsr_readcbk:1], [], [], [], [], [], [], [], [], [], [], []
                    Time/Line Number=Wed Mar 21 14:31:07 2012/138809

                    This problem for clob was solved by recent Oracle patches ... but using an xmltype still throw a null pointer exception ...

                    Edited by: 924221 on 07-May-2012 22:35
                    • 7. Re: SQL Xml issue in ojdbc 6 (
                      I am also hitting this. I would really like to use the binary storage as XMLType performance has been some what of a concern for us. It is especially concerning as the default storage was switched to binary and this one kind of crept up on us.

                      Has any one had any success?
                      • 8. Re: SQL Xml issue in ojdbc 6 (
                        Hi All,

                        someone solved this problem?

                        It seems that ojdbc ignores XML_TYPE fields in resultset generation.

                        Have a nice day!

                        • 9. Re: SQL Xml issue in ojdbc 6 (
                          I am also desperated for an answer.
                          • 10. Re: SQL Xml issue in ojdbc 6 (
                            I do not have an answer, but I might have a clue.

                            The same code for a custom UserType does work under JBoss 6.1 if the xmlparserv2, xdb6, and ojdbc6 JAR's are in the deployment directory, so my suspicion is that this has something to do with the new classloader in JBoss 7. I myself have created a module for the Oracle driver and added these JAR's as resource-roots, but I still see the same thing. Has anyone tried posting this on the JBoss forums to see what they might be able to tell us?