Jpa call stored procedure with output cursor
I am trying to call an oracle stored procedure that returns a usign JPA exit cursor 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 well, but if I try to change the procedure definition to change the cursor as the second parameter and make the appropriate parameter changes in the JPA code, it doesn't work. I get an 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 when calling 'stored_proc'
create or replace PROCEDURE stored_proc(inputParam IN NUMBER,ret_cursor OUT sys_refcursor)
Should the output cursor always be the first parameter in the stored proc when using JPA? Is there a workaround?
source to share
As Uday says, currently the OUT parameter that you want to use to return results must be the first parameter.
That said, GitHub support is already in place to improve CallableStatement support if you'd like to 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 functionality of the stored procedure that we are adding as part of the JPA 2.1 EG.
source to share
Both Uday and Steve are right.
At this point, the OUT parameter should be the first argument.
And we have to wait until JPA 2.1 is released to get stored procedure support.
you can look at JPA 2.1 Spec here
If you want to implement jpa 2.1 right now, EclipseLink is ahead of the curve. you can find the JPA 2.1 implementation status here from EclipseLink - http://wiki.eclipse.org/EclipseLink/Development/JPA_2.1 and examples here - http://wiki.eclipse.org/EclipseLink/Examples/JPA/StoredProcedures
Also you can visit Arun Gupta's JPA 2.1 main events page at https://blogs.oracle.com/arungupta/entry/jpa_2_1_early_draft
source to share