Sql-query with OUT Param Hibernate 5

Hibernate ORM
1

We are upgrading a legacy app from Hibernate 4 to 5. The app uses old-style HBM files. We had a working Query that used this form (where ? is a REF_CURSOR OUT parameter in an Oracle proc):

  <sql-query name="get_stuff" callable="true">
    <return class="com.SomeCustomClass"/>
    { call GET_STUFF(? :param_one, :param_two) }
  </sql-query>

Which we were able to call in Hibernate 4 using:

    Query q = session.getNamedQuery("get_stuff");  
    q.setParameter("param_onee", "val1"
    q.setParameter("param_two", "val2");
    List<SomeCustomClass> olist = q.list();

With the org.hibernate.query.Query class in Hibernate 5 we get the ‘mixed parameter strategy’ using the above syntax. The problem is I have not been able to find the right syntax to get the above to work in Hibernate 5. I have tried everything I can imagine. Can anyone help with the proper way to do the above in Hibernate 5? I am not finding good examples with OUT params.

Thanks

2

Did you try this yet?

{ ? = call GET_STUFF(:param_one, :param_two) }

alternatively try

{ call GET_STUFF(:param_one, :param_two) }

which might also just work.

3

Yes I have tried both. I believe the first gives the ‘mixed param strategy’ and the latter gives ‘wrong number of parameters’

4

Not sure what to tell you. The first one should work just fine. We even have a test for this. See hibernate-orm/CustomStoredProcTestSupport.java at 5.6 · hibernate/hibernate-orm · GitHub

Are you sure you updated to Hibernate 5 and not to 6? I don’t think we error out on mixed parameters in 5 yet.

5

I appreciate you looking into this. I am aware our setup with HBM is considered highly deprecated at this point.More detail below. Given the below detail, it looks like I need a way to represent the ‘?’ output parameter in a way that does not lead to the mixed parameter exception (?)

Hibernate version is 5.6.9-FINAL

HMB now looks (as suggested):

  <sql-query name="get_Stuff" callable="true">
    <return class="com.SomeCustomClass"/>
    { ? = call GET_SERIES(:param_one, :param_two) }
  </sql-query>

The old HBM which worked under Hibernate 4 was:

  <sql-query name="get_Stuff" callable="true">
    <return class="com.SomeCustomClass"/>
    { call GET_SERIES(?, :param_one, :param_two) }
  </sql-query>

Our Java code is of this form (and has not changed at all):

Session session = super.getSession();
    Query q = session.getNamedQuery("get_Stuff");
    q.setParameter("param_one", something
    q.setParameter("param_two", something);
    List<SomeCustomClass> olist = q.list();

The suggested HBM XML leads to:

08:53:53.274 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_STUFF'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The older form of the HBM running on Hibernate 5 (which worked under Hibernate 4) leads to:

org.hibernate.engine.query.ParameterRecognitionException: Mixed parameter strategies - use just one of named, positional or JPA-ordinal strategy

6

Are you sure that your stored procedure really has just 2 parameters, and not 3 where the first one is an INOUT parameter? The error comes from Oracle, so it seems the Hibernate side is working fine.

Please post the definition of the stored procedure.

7

In our tests, the procedure is defined like this:

CREATE OR REPLACE FUNCTION simpleScalar (j number)
            RETURN SYS_REFCURSOR AS st_cursor SYS_REFCURSOR;
            BEGIN
            OPEN st_cursor FOR
            SELECT j as value, 'getAll' as name from dual;
            RETURN st_cursor;
            END;
8

Yes the first ‘?’ is an OUT param. Nothing has changed in Oracle, so something has changed on the Hibernate side. I think I need to know how to represent the OUT param in the call using something other than ‘?’ that will not cause the mixed param strategy error.

create or replace PROCEDURE "GET_STUFF"
  (
    v_result OUT SYS_REFCURSOR,
    param1         IN VARCHAR2,
    param2    IN VARCHAR2 )
9

I don’t know how this worked before in Hibernate 4, but it looks like you should be registering a third parameter then:

  <sql-query name="get_Stuff" callable="true">
    <return class="com.SomeCustomClass"/>
    { call GET_SERIES(:result, :param_one, :param_two) }
  </sql-query>

Maybe the Oracle JDBC driver does some magic to return the first OUT parameter as result set. Either way, I think the more compatible way would be to change the stored procedure definition to use:

create or replace PROCEDURE "GET_STUFF"
  (
    param1         IN VARCHAR2,
    param2    IN VARCHAR2 )
            RETURN SYS_REFCURSOR AS v_result SYS_REFCURSOR;
10

Thanks. Your second suggestion looks like our avenue. Thanks again.