Hi,
I am using a NamedPLSQLStoredProcedureQuery to run a stored procedure on an Oracle 11g database.
The procedure is defined as:
PROCEDURE update_rule_value_rec
(
id_in IN VARCHAR2(50),
action_rec_in IN action_rec_type,
result_count_out OUT NUMBER,
result_cur_out OUT SYS_REFCURSOR
);
and the Procedure is defined in Java as:
@NamedPLSQLStoredProcedureQuery(
name="createSAV3",
procedureName="SAR_OPS.UPDATE_RULE_VALUE_REC",
parameters= {
@PLSQLParameter(queryParameter="id_in", name="id_in",direction=Direction.IN),
@PLSQLParameter(queryParameter="action_rec_in", name="action_rec_in", direction=Direction.IN, databaseType="SAR_OPS.ACTION_REC_TYPE"),
@PLSQLParameter(queryParameter="result_count_out", name="result_count_out",direction=Direction.OUT),
@PLSQLParameter(queryParameter="result_cur_out", name="result_cur_out", direction=Direction.OUT, databaseType="SYS_REFCURSOR")
}
)
I have added the necessary @Embeddable, @Struct and @PLSQLRecord annotations to my Java class ActionRecIn.java.
I pass the ActionRecIn.java into the stored procedure in the parameter 'action_rec_in' and the stored procedure is called as expected:
public List<SuggestedActionValue3> create(ActionRecIn ari) {
Query query = em.createNamedQuery("createSAV3");
query.setParameter("id_in", "DUMMY");
query.setParameter("suggested_action_rec_in", ari);
query.getResultList();
}
The resulting Object returned from the query.getResultList() seems to be a list of the returned obejcts and does contain the 'result_count_out' parameter and it is correctly set to 1... however, im not seeing any other objects in the list. I was expecting to see another object that had a list of objects from my 'result_cur_out'.
Just to make it clear... right now in my stored procedure the result_cur_out is a SYS_REFCURSOR which contains records on type SAR_OPS.ACTION_REC_TYPE (the same record i passed in). For testing i am passing back out the record i gave in but im struggling to see it with the SYS_REFCURSOR.
Underneath my stored procedure fills the 'result_cur_out' as:
PROCEDURE open_result_cur
(
result_cur_out OUT action_refcur_type
IS
BEGIN
OPEN result_cur_out FOR
SELECT BLAH BLAH BLAH FROM BLAH
END open_result_cur;
where 'action_refcur_type' is a REF CURSOR returning the RECORD type passed in:
TYPE action_refcur_type IS REF CURSOR RETURN action_rec_type
] 
Search
Help
Register
Login
Home
