This discussion is archived
10 Replies Latest reply: Dec 27, 2012 1:13 PM by 941189 RSS

SQL Xml issue in ojdbc 6 (11.2.0.3.0)

Jude Newbie
Currently Being Moderated
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 (11.2.0.3.0)
    Acooper Explorer
    Currently Being Moderated
    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 (11.2.0.3.0)
    Jude Newbie
    Currently Being Moderated
    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;
              try
              {
                   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 (11.2.0.3.0)
    927224 Newbie
    Currently Being Moderated
    Hello,

    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
    );

    JAVA:

    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);

    tmpStatement.execute();

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

    while(tmpResultSet.next())
    {

    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>");
    else
    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 :
    java.lang.NullPointerException
    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 (11.2.0.3.0)
    928501 Newbie
    Currently Being Moderated
    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 (11.2.0.3.0)
    Jude Newbie
    Currently Being Moderated
    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 (11.2.0.3.0)
    927224 Newbie
    Currently Being Moderated
    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
    Severity=Critical
    Acknowledged=No



    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 (11.2.0.3.0)
    953841 Newbie
    Currently Being Moderated
    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 (11.2.0.3.0)
    965670 Newbie
    Currently Being Moderated
    Hi All,

    someone solved this problem?

    It seems that ojdbc ignores XML_TYPE fields in resultset generation.


    Have a nice day!

    E.
  • 9. Re: SQL Xml issue in ojdbc 6 (11.2.0.3.0)
    975011 Newbie
    Currently Being Moderated
    I am also desperated for an answer.
  • 10. Re: SQL Xml issue in ojdbc 6 (11.2.0.3.0)
    941189 Newbie
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points