java.sql.SQLException: Invalid column type: 2012 when using Hibernate and Oracle stored procedure with REF_CURSOR


#1

Hi to everybody,
i am changing the version of weblogic from 12.1.1 to 12.2.3.0.

I try also the application on weblogic 12.1.3 with java 8 and work.

I have this named Stored Procedure:

		@NamedStoredProcedureQuery(name = "Named", procedureName = "Procedure", resultClasses = Test.class, parameters = {
				@StoredProcedureParameter(name = "results_cursor", mode = REF_CURSOR, type = void.class),
				@StoredProcedureParameter(name = "CODE", mode = IN, type = BigInteger.class),

The procedure is similar to something like that:

PROCEDURE Procedure(results_cursor OUT SYS_REFCURSOR, CODE IN NUMBER);

and after upgrade I get this kind of exception


Caused by: java.sql.SQLException: Invalid column type: 2012
        at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.
va:3978)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInte
al(OracleCallableStatement.java:140)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInte
al(OracleCallableStatement.java:2401)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(Ora
eCallableStatement.java:2307)
        at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParame
r(OracleCallableStatementWrapper.java:1250)
        at weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCa
ableStatementWrapper.registerOutParameter(Unknown Source)
        at org.hibernate.engine.jdbc.cursor.internal.StandardRefCursorSupport.
gisterRefCursorParameter(StandardRefCursorSupport.java:69)
        ... 118 common frames omitted

I read on stack overflow that is just necessary an upgrade on Oracle driver of Weblogic or as workaround use different way to register procedure. But it is really true?

This is the link: https://stackoverflow.com/questions/46175576/hibernate-fail-to-register-ref-cursor-parameter

Because in first case, I have to work on Weblogic, but this mean to ask to system manager to change the Weblogic installation, in second case it is not possible use JPA annotation.

Do i miss something? Does somebody has occured the same problem?

Thanks to everybody for any support.

Luca

PS: I check also the value of Oracle Cursor and seem to be or CURSOR = -10 and in Java.sql.Types.REF_CURSOR=2012.

Can be that the problem?


#2

In this article, I have an example for calling a REF_CURSOR stored procedure with JPA and Hibernate:

Assuming this is the stored procedure:

CREATE OR REPLACE PROCEDURE post_comments ( 
   postId IN NUMBER, 
   postComments OUT SYS_REFCURSOR ) 
AS 
BEGIN
    OPEN postComments FOR
    SELECT *
    FROM post_comment 
    WHERE post_id = postId; 
END;

You simply call it like this:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("post_comments")
    .registerStoredProcedureParameter(1, Long.class, 
         ParameterMode.IN)
    .registerStoredProcedureParameter(2, Class.class, 
         ParameterMode.REF_CURSOR)
    .setParameter(1, 1L);
 
query.execute();
 
List<Object[]> postComments = query.getResultList();

We also have a test in hibernate code base and this is the Driver we use:

com.oracle.jdbc:ojdbc7:12.1.0.2

As for WebLogic, you have to ask Oracle on their support forums since they surely knoww it better.


#3

Hi Vlad,
i have tried on Weblogic 12.2.1.3 with oracle driver ojdbc8.

I have downloaded the source code of hibernate and i have tried to make a
hard fix, in the specific case i have fixed the
class StandardRefCursorSupport changing the method:

private int refCursorTypeCode() {
return  Types.REF_CURSOR;

}

to this version:


private int refCursorTypeCode() {

return -10;
}

I take the value -10 from the class oracle.jdbc.OracleTypes.CURSOR, of the
ojdbc oracle driver.

Looking from Hibernate class I also read this comment,

public static boolean supportsRefCursors(DatabaseMetaData meta) {
// Standard JDBC REF_CURSOR support was not added until Java 8, so we need
to use reflection to attempt to
// access these fields/methods...

and the value Types.REF_CURSOR is taken from JDK in hibernate: does the
REF_CURSOR numeric value become standard in Java 8?
So I don’t know if is a bug of hibernate or in ojdbc8 driver , in case of
ojdbc8 driver it will take too much time for my project delivery time so I
have to find a solution and also in hibernate i will don’t have tomorrow
the solution.

I see that the class StandardRefCursorSupport is referenced using the
interface, like more or less a Bean Context, can i create a custom
implementation replacing the default one?

Do you know how to provide to Hibernate my Custom implementation with the
hardcode solution to Hibernate whitout recompile that in order to
deactivate without any problem after the final fix?

Thanks in advance for any support.

Luca


#4

Yes, it’s been added in Java 8.

The Oracle Dialect can deal with the Oracle-specific REF_CURSOR as well:

statement.registerOutParameter( col, OracleTypesHelper.INSTANCE.getOracleCursorTypeSqlType() );

The StandardRefCursorSupport decides whether to use the JDBC 4.2 REF_CURSOR or the DB-specific one based on:

jdbcServices.getExtractedMetaDataSupport().supportsRefCursors()

Now, since you are using ojdbc8, which supports JDBC 4.2, it will use the Types.REF_CURSOR while the database server does not support that.

So, it means you are using an older Oracle server (e.g. Oracle 10, 11) and that’s the problem, not Hibernate.

You don’t need to change anything in Hibernate to fix this issue. What you need to do is switch to an older JDBC driver which matches the Oracle server version like ojdbc6.jar.