'jpa calling stored procedure with output cursor

I am trying to call an oracle stored procedure that returns an output cursor usign JPA as follows

  create or replace PROCEDURE stored_proc(ret_cursor OUT sys_refcursor, inputParam IN NUMBER)
   -- body 
  END stored_proc;

  @Entity
  @NamedNativeQuery(name = "callStoredProc",  
         resultClass = Result.class,  
         query = "{call stored_proc(?,:inputParam)}",  
        callable = true,
        readOnly=true
  )
  public class Result{
    // map the result set params.
  }  

  //JPA code to get result set 
  List<Result> resultList =  getEntityManager().createNamedQuery("callStoredProc")
                                        .setParameter("inputParam", inputParam)
                            .getResultList();

This all works good but, if I try to alter the procedure definition to change the cursor as second parameter and make corresponding parameter changes in JPA code as well , it doesn't work. I get the error

[4/30/12 11:42:30:505 CDT] 00000025 SystemErr R Caused by: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'stored_proc'

    create or replace PROCEDURE stored_proc(inputParam IN NUMBER,ret_cursor OUT sys_refcursor)

Should the output cursor be always the first parameter in the stored proc when using JPA? Is there a workaround for it?

hibernate jpa

Solution 1:^[1]

Yes, if you are using Hibernate as your provider you will need to use an OUT parameter as the first parameter.

See the documentation.

Solution 2:^[2]

As Uday says, currently the OUT parameter you want to use to return results has to be the first parameter.

That said, support is already in place on GitHub master for improved CallableStatement support if you wanted to take a peek (and even give feedback): https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/StoredProcedureCall.java

StoredProcedureCall spc = session.createStoredProcedureCall( "stored_proc", Result.class )
spc.registerStoredProcedureParameter( 2, ResultSet.class, ParameterMode.REF_CURSOR );
// registering IN parameters is generally optional

// thinking I will change this method name to execute()....
StoredProcedureOutputs spo = spc.getOutputs();
StoredProcedureReturn spr = spo.getNextReturn();
assert spr.isResultSet();
StoredProcedureResultSetReturn sprsr = (StoredProcedureResultSetReturn) spr;
List<Result> results = (List<Result>) sprsr.getResultList();

This is all to support the stored procedure functionality we are adding as part of the JPA 2.1 EG.

Solution 3:^[3]

Both Uday and Steve are right.

As of now, the OUT parameter should be the first argument.

And we have to wait till the release of JPA 2.1 to get the support for stored procedures.

you can have look at the JPA 2.1 Spec here

If you want the jpa 2.1 implementation right now, EclipseLink are ahead in that race. you can find the JPA 2.1 implementation status by EclipseLink here - http://wiki.eclipse.org/EclipseLink/Development/JPA_2.1 and examples here - http://wiki.eclipse.org/EclipseLink/Examples/JPA/StoredProcedures

Also you may like to visit the page of JPA 2.1 highlights by Arun Gupta in https://blogs.oracle.com/arungupta/entry/jpa_2_1_early_draft

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Martijn Pieters
Solution 2	Steve Ebersole
Solution 3	rudrasiva86

'jpa calling stored procedure with output cursor

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]