SQLException when I try to use SQLXML instead of XMLType as output type from Oracle function

Question

I am trying to refactor my SpringBoot application and replace using XMLType in favor of java.sql.SQLXML as some of the methods related to XMLType are already deprecated and I want to rewrite it.

I found in the documentation that I should - instead of deprecated:

Document doc = xmltype.getDocument();

use:

DOMSource domSource = sqlxml.getSource (DOMSource.class);
Document document = (Document) domSource.getNode();

But I have a problem with that. When I try to do this, I get an exception.

Here is fragment of my code:

            String xmlInStr;  
            XMLType xmlIn = null;  
            XMLType xmlOut = null;  
            SQLXML sqlxmlIn = null;  
            SQLXML sqlxmlOut = null;  
            OracleConnection conn = null;  
            Connection hikariConn = null;  
    (...)  
            try {  
                hikariConn = jdbcTemplate.getDataSource().getConnection();  
                sqlxmlIn = hikariConn.createSQLXML();  
                sqlxmlIn.setString(xmlInStr);  
                logger.debug("Input xml has been set.");  
                // input params  
                SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)  
      
                    .withFunctionName("getQuestionnaireByType")  
                    .withReturnValue()  
                    .withoutProcedureColumnMetaDataAccess()  
                    .declareParameters(  
                            new SqlOutParameter("RETURN",OracleTypes.SQLXML),  
                            new SqlParameter("vLang",OracleTypes.VARCHAR),  
                            new SqlParameter("vRefId",OracleTypes.VARCHAR),  
                            new SqlParameter("vSrcId",OracleTypes.VARCHAR),  
                            new SqlParameter("vUserId",OracleTypes.VARCHAR),  
                            new SqlParameter("vQueType",OracleTypes.VARCHAR),  
                            new SqlParameter("vXmlDataIn",OracleTypes.SQLXML)  
                    )  
                ;  
                jdbcCall.setAccessCallParameterMetaData(false);  
                jdbcCall.setReturnValueRequired(true);  
                jdbcCall.withSchemaName("atr_adap");  
      
                SqlParameterSource in = new MapSqlParameterSource()  
                        .addValue("vLang", rhData.getLocale().getLanguage())  
                        .addValue("vRefId", rhData.getRequestId())  
                        .addValue("vSrcId", rhData.getSrcId())  
                        .addValue("vUserId", rhData.getUserId())  
                        .addValue("vQueType", queType)  
                        .addValue("vXmlDataIn", sqlxmlIn);  
                          
                // calling db (with XMLType)  
                xmlOut = jdbcCall.executeFunction(XMLType.class, in);  
                  
                logger.debug("Stored Procedure {} executed (XMLType)", jdbcCall.getProcedureName());  
                String xmlOutStr = null;  
                xmlOutStr = xmlOut.getString();  
                logger.info("xml out (XMLType)\r\n{}", xmlOutStr);  
                doc = xmlOut.getDocument(); // everything ok (but getDocument deprecated ...)  
                  
                // calling db (with SQLXML)  
                sqlxmlOut = jdbcCall.executeFunction(java.sql.SQLXML.class, in);  
                logger.debug("Stored Procedure {} executed (SQLXML)", jdbcCall.getProcedureName());  
                xmlOutStr = sqlxmlOut.getString();  
                logger.info("xml out (SQLXML)\r\n{}", xmlOutStr); // everything ok  
                DOMSource domSource = sqlxmlOut.getSource(DOMSource.class); // the following exception is throwing from here  
                doc = (Document) domSource.getNode();   
(...) 

java.sql.SQLException: Attempt to read a SQLXML that is not readable. 
    at oracle.xdb.XMLType.getSource(XMLType.java:5159)
    at my.package.JdbcQuestionnairesRepository.findByType(JdbcQuestionnairesRepository.java:239)
    at my.package.JdbcQuestionnairesRepository$$FastClassBySpringCGLIB$$a9555145.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at my.package.JdbcQuestionnairesRepository$$EnhancerBySpringCGLIB$$a184900c.findByType(<generated>)
    at my.package.QuestionnairesService.getQuestionnaireByType(QuestionnairesService.java:48)
(...)   

What I am doing wrong?

Best regards,
koli

Answer 1

The problem appears to be the line xmlOutStr = sqlxmlOut.getString();. Remove this line, and the line which writes xmlOutStr to the log, and your code should work.

The JavaDoc for the JDBC SQLXML interface also mentions the following:

The state moves from readable to not readable once free() or any of the reading APIs are called: getBinaryStream(), getCharacterStream(), getSource(), and getString().

I can't say why XMLType.getDocument() doesn't throw an exception even after .getString() is called.