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

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:

	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

I pass the 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);


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
      OPEN result_cur_out FOR

   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: Tue Apr 16 02:29:13 GMT 2024

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

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

Back to the top