StoredProcedure with DBMS_SQL.VARCHAR2_TABLE as output parameter

[Andreas Senft]

I just started experimenting with Spring's StoredProcedure support. Simple cases are working fine. However, this one seems to be tricky.

I have a stored procedure (Oracle 9i) with the following signature:



PROCEDURE test(o_date OUT DATE, o_tbl OUT DBMS_SQL.VARCHAR2_TABLE, i_val IN NUMBER)


Now I have a problem declaring the second parameter. I tried this:


declareParameter(new SqlOutParameter("o_tbl", Types.ARRAY, "DBMS_SQL.VARCHAR2_TABLE", new SqlReturnType() {

public Object getTypeValue(CallableStatement pCs, int paramIndex, int pSqlType, String pTypeName) throws SQLException {
// TBD
}

}));

However I received an exception:


org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL [{call tg_dev.test(?, ?, ?)}]; SQL state [null]; error code [17074]; Ungültiges Namensmuster: DBMS_SQL.VARCHAR2_TABLE; nested exception is java.sql.SQLException: Ungültiges Namensmuster: DBMS_SQL.VARCHAR2_TABLE
java.sql.SQLException: Ungültiges Namensmuster: DBMS_SQL.VARCHAR2_TABLE
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:162)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(Ora cleTypeADT.java:412)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeA DT.java:327)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescri ptor.java:1300)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:156)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayD escriptor.java:113)
at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName (NamedTypeAccessor.java:92)
at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeA ccessor.java:89)
at oracle.jdbc.driver.T4CCallableStatement.allocateAc cessor(T4CCallableStatement.java:500)
at oracle.jdbc.driver.OracleCallableStatement.registe rOutParameterInternal(OracleCallableStatement.java :129)
at oracle.jdbc.driver.OracleCallableStatement.registe rOutParameter(OracleCallableStatement.java:168)
at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:201)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:803)
at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:842)
at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:100)

"Ungültiges Namensmuster" means "invalid name pattern".

Has someone an idea about how to declare this correctly?

Thanks in advance,
Andreas

P.S.: The stored procedure itself is working fine, when invoked from a database client.

[trisberg]

I have never had any luck using types defined in a package as the return types - always had to declare the types on their own outside of the package. This seems to be a limitation in Orcale JDBC. If you can figure out how to do this in plain JDBC then I can help translate that to Spring JDBC.

[Andreas Senft]

Thanks for the offer. Tomorrow I'll try to dig a bit further into this.

BTW: When I do not specify the "name" parameter on declaring the SqlOutParameter, I get another exception about specifying wrong number or type of arguments. I just wonder about that one, since from the API documentation I understand that this parameter should be optional.

From my investigations so far I did only see examples using explicit types (with create type) without packages, but I will try further. I do not want to create a new type in the existing schema (unless absolutely necessary), so I try to use the predefined type first.

If I can find out any news I will post them here. If anyone else can provide informations about this issue feel free to share it :)

Regards,
Andreas

[trisberg]

The name is needed on the out parameters since they are placed in a Map with the name as the key. The order they are declared is also important - left to right as they would appear in the JDBC call statement.

[Andreas Senft]

My bad. I didn't mean the "name" parameter, but the "typeName" parameter. That one is stated to be optional in the API documentation. Has been late yesterday.

Regards,
Andreas

[Andreas Senft]

I investigated a little further and came to the conclusion that it seems to be not possible to use DBMS_SQL.VARCHAR2_TABLE as parameter and access it from Java. So I guess I have to create an according type in the database (at least this works).

Regards,
Andreas