Hibernate: Calling stored procedure returns cursor

Question

In Hibernate 4.2 I try to call stored procedure that returns cursor (I use Oracle DB).

Procedure looks like:

PROCEDURE  SYS_columnNames ( ownerIn in sys.all_tab_cols.owner%type,
                              tableName in sys.all_tab_cols.table_name%type,
                              resultCur out curRef )
is
begin
  open resultCur for
    select column_name from sys.all_tab_cols
     where owner =  ownerIn
         and Upper(Trim(table_name)) = tableName 
    order by column_name;
exception
  when others then
    null;
end SYS_columnNames ;

And my Java code:

Session session = null;
try
{
    SessionFactory sessionFactory = sessionFactoryManager.getCurrentSession();
    session = sessionFactory.openSession();
    return session.doReturningWork( new ReturningWork< T >()
    {
        @SuppressWarnings( "unchecked" )
        @Override
        public T execute( Connection aConnection ) throws SQLException
        {
            CallableStatement callstm = null;
            try
            {
                String functionCall = "{call " + aProcedureName + "(:owner, :tableName, :rescur)}";
                callstm = aConnection.prepareCall( functionCall );
                callstm.setString( "owner", "MYOWNER" );
                callstm.setString( "tableName", "USER_TABLE" );
                callstm.registerOutParameter( "rescur", OracleTypes.CURSOR );
                callstm.execute();
                return (T)callstm.getObject( 3 );
            }
            finally
            {
               closeQuietly( callstm );
            }
        }
    } );
}
finally
{
   closeQuietly( session );
}

But this give me an error:

org.hibernate.exception.GenericJDBCException: error executing work
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:289)
    at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:1953)
    at org.hibernate.internal.SessionImpl.doReturningWork(SessionImpl.java:1949)

(...)
Caused by: java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!
    at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:2027)
    at oracle.jdbc.driver.OracleCallableStatementWrapper.getObject(OracleCallableStatementWrapper.java:816)
    at org.hibernate.jdbc.WorkExecutor.executeReturningWork(WorkExecutor.java:72)
    at org.hibernate.internal.SessionImpl$3.accept(SessionImpl.java:1946)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:284)
    ... 58 more

I totally don't know how to call stored procedure that returns cursor. Any help will be appreciated.

Hankypanky

I know what I did wrong... There should be: String functionCall = "{call " + aProcedureName + "(?, ?, ?)}"; callstm = aConnection.prepareCall( functionCall ); callstm.setString( 1, "MYOWNER" ); callstm.setString( 2, "USER_TABLE" ); callstm.registerOutParameter( 3, OracleTypes.CURSOR ); – Hankypanky

Nice, then answer your own question and accept it after two days. You have managed to find the answer for your own question. – Cytaster

Answer 1

Solution for that is to use '?' instead of variable names:

String functionCall = "{call " + aProcedureName + "(?, ?, ?)}"; 
callstm = aConnection.prepareCall( functionCall ); 
callstm.setString( 1, "MYOWNER" ); 
callstm.setString( 2, "USER_TABLE" ); 
callstm.registerOutParameter( 3, OracleTypes.CURSOR );

Hankypanky

Answer 2

Problem is in one of the below statements

callstm.registerOutParameter( "rescur", OracleTypes.CURSOR );
callstm.execute();
return (T)callstm.getObject( 3 );

in above code when you are setting up the parameter at line 1; you are using named parameter binding while when you are fetching the result back you are using ordinal binding i.e. line 3; if you have line 3 as named binding like below; then it will work fine

return (T)callstm.getObject("rescur");

I used the same code as yours; by just correcting the return statement and it worked like a charm.

Happy coding!

Galvez