Announcement Announcement Module
Collapse
No announcement yet.
Stored Procedure and oracle.xdb.XMLType Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored Procedure and oracle.xdb.XMLType

    Using Spring's support for stored procedures, how can I specify a parameter of type oracle.xdb.XMLType?

    Currently, the constructor of my subclass of org.springframework.jdbc.object.StoredProcedure contains statements like the following to declare a parameter:

    declareParameter(new SqlParameter({PARAM_NAME}, {PARAM_TYPE}));

    The PARAM_TYPE argument is an integer constant from the class java.sql.TYPES. There is no value in java.sql.TYPES to represent XMLType.

    Is there an alternate approach to declaring a stored procedure parameter, when the stored procedure is expecting XMLTYPE?

    TIA,
    Ed

  • #2
    You can use the value from OracleTypes

    Comment


    • #3
      Actually you should search in the forums for more details. According to this thread, there are some issues using XMLType.

      Comment


      • #4
        Ed,

        I'm currently looking at providing a Spring solution for the XML types. That's still in early stages so for now you will have to rely on Oracle specific extensions.

        If you can provide a "plain" jdbc solution of how to pass in the XML to a stored proc, then I can probably show you how to do the same using current Spring features.

        Comment


        • #5
          Thomas,

          Thanks for your help. The attached class contains the method, saveDocument, which invokes a stored procedure with an XMLTYPE parameter. Here is the specific statement:

          anOracleCallableStatement.setObject(11,new XMLType(oraConnection,documentDvo.getContent()));

          Regards,
          Ed

          Comment


          • #6
            I would try adding this declaration for the XML parameter:

            Code:
            declareParameter(new SqlParameter("xml", Types.OTHER));
            and then add the following SqlTypeValue as the value in the inputMap:

            Code:
            inputMap.put("xml",
                    new SqlTypeValue() {
                        public void setTypeValue(PreparedStatement cs, int colIndx, int sqlType, String typeName) throws SQLException {
                            ((OracleCallableStatement)cs).setObject(11, new XMLType(cs.getConnection(), documentDvo.getContent()));
                        }
                    });
            Not exactly pretty, but it might work. You might have to add a NativeJdbcExtractor depending on whether you are in a connection pool or not. Sometimes the cs.getConnection returns the native connection so it's not always necessary even when you do use a connection pool that wraps the CallableStatement.

            Comment


            • #7
              Thanks. I'll try this and let you know the results.

              Comment


              • #8
                Hi Thomas,

                I implemented your suggestion, and it worked after I assigned the argument to my execute method, documentDvo, to a final temp variable within the method:

                Code:
                 final DocumentDVO finalDocDvo = documentDvo;
                However, when I tested with a large xml document, approx. 6Mb, it threw the follow exception:
                Code:
                org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call spt_update(?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [72000]; error code [20001]; ORA-20001: Error from ed.spt_update==>ORA-19011: Character string buffer too small
                ORA-06512: at "SYS.XMLTYPE", line 163
                ORA-06512: at "ETREMBIC.SPT_UPDATE", line 45
                ORA-19011: Character string buffer too small
                ORA-06512: at line 1
                I was testing locally with OracleXE 10g, so it may be an Oracle configuration issue. I'll look into that and post what I find.

                Cheers,
                Ed

                Comment


                • #9
                  The cause of the exception was a test in the stored procedure of the XMLType argument. The argument is declared with a default value in the stored proc as follows:
                  Code:
                      p_claim_attribute_xml_in IN        CLAIM_ATTRIBUTE_CLAR.claim_attribute_xml%TYPE DEFAULT XMLTYPE('<Default></Default>'),
                  Setting the corresponding column in an update statement was as follows:
                  Code:
                  claim_attribute_xml = DECODE(p_claim_attribute_xml_in.getStringVal(),'<Default></Default>',claim_attribute_xml,p_claim_attribute_xml_in)
                  The getStringVal() method caused the exception, as it is limited to 4,000 characters. When I removed it, I was able to execute the stored procedure with a 6Mb xml argument. I'm checking with our DBAs for a work-around in the stored proc.

                  Comment

                  Working...
                  X