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

Hibernate ORM
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.

5

we have similar issue and getting error invalid column type 2012 error when we call stored procedure.
we are using weblogic 12.2.13, ojdbc8, oracle database 12.1.0.2, hibernate and hibernate jpa.
I see in the above comment that hibernate tested stored procedure call with ojdbc7.jar.
It looks like a bug either in ojdbc8 or hibernate code and it is causing issue when we call stored procedure.
Please provide us solution to this issue.

6

The issue seem to be related to how driver oracle provide the number of Types.REF_CURSOR, which is not compliance to JDBC 4.2.

I don’t know if is just related to hibernate or Oracle, the solution can be:

  1. put ojdbc7 in weblogic instead ojdbc8
  2. use JVM 7 (i am not 100% sure)
  3. make a custom build of hibernate with the fix as workaround where the type number of REF_CURSOR is the old release
  4. ask to oracle to make a check in their own driver
7

I just upgraded the Oracle version to ojdb8 and everything works fine. Check out this test which uses REF_CURSOR and compare it to yours and see if there’s any difference.

8

Hi!

Could you please tell why I have the same issue with Oracle 12.2.0.1.0?
And it’s WebLogic 12.2.1.3.0 too.
Hibernate 5.4.14.

P.S. I’ve found the answer on Oracle site:
" Java SE 8 has new APIs for JDBC 4.2 that are supported for versions of WebLogic Server 12.1.3 and later that are running on Java SE 8 with a JDBC driver that supports JDBC 4.2. However, although the Oracle JDBC thin driver bundled with WebLogic Server is certified on Java SE 8, the Oracle JDBC thin driver does not support JDBC 4.2. The Derby 10.10 driver that is shipped with Oracle WebLogic Server as of release 12c (12.2.1) has been tested with JDBC 4.2 and may be used. The corresponding Derby documentation is available at http://db.apache.org/derby/docs/10.10/ref/rrefjdbc4_2summary.html ."