Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Issue getting SYS_REFCURSOR OUT NamedPLSQLStoredProcedureQuery
Issue getting SYS_REFCURSOR OUT NamedPLSQLStoredProcedureQuery [message #1513566] Tue, 16 December 2014 16:57
Craig Jones is currently offline Craig JonesFriend
Messages: 5
Registered: December 2014
Junior Member
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


Previous Topic:Eclipselink cache sync
Next Topic: "Exception Description: Modify queries require an object to modify"
Goto Forum:
  


Current Time: Sat Mar 02 11:37:53 GMT 2024

Powered by FUDForum. Page generated in 0.02120 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top