Stored Procedure and oracle.xdb.XMLType

[eguy66]

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

[wpoitras]

You can use the value from OracleTypes

[wpoitras]

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

[trisberg]

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.

[eguy66]

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

[trisberg]

I would try adding this declaration for the XML parameter:

declareParameter(new SqlParameter("xml", Types.OTHER));

and then add the following SqlTypeValue as the value in the inputMap:

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.

[eguy66]

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

[eguy66]

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:

 final DocumentDVO finalDocDvo = documentDvo;

However, when I tested with a large xml document, approx. 6Mb, it threw the follow exception:

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

[eguy66]

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:

    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:

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.