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(OracleTypeADT.java:412)
    at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:327)
    at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1300)
    at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:156)
    at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:113)
    at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:92)
    at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:89)
    at oracle.jdbc.driver.T4CCallableStatement.allocateAccessor(T4CCallableStatement.java:500)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:129)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:168)
    at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:201)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:803)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:842)
    at org.springframework.jdbc.object.StoredProcedure.execute(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