CODEWITHSUNDEEP
javaoracle-databasehibernatecursor
emmedierre
emmedierre

Reputation: 21

read data from SYS_REFCURSOR in a Oracle stored procedure and reuse it in java

I have this table:

  CREATE TABLE "QMS_MODEL"."BOOKING" (
       "ID" NUMBER ( 19, 0 ) CONSTRAINT "QMS_BOOKING_NN_1" NOT NULL ENABLE
      ,"CALL_TIME" TIMESTAMP ( 6 )
  );

Then i have a simple stored procedure in Oracle that: 1.get a record from a table 2.update a column on the found record 3.returns by an OUT parameter a SYS_REFCURSOR that points to the found record :

CREATE OR REPLACE 
PROCEDURE GET_BOOKING
  ( 
    refCursorValue OUT SYS_REFCURSOR,
    bookingId IN QMS_MODEL.booking.id%type
  )
AS
    bookingResult QMS_MODEL.booking%ROWTYPE;
    todayAtNow QMS_MODEL.booking.booking_time%type;
BEGIN

  --********************************
  --get booking cursor....
  --********************************
  OPEN refCursorValue FOR 
  SELECT 
    bb.*
  FROM qms_model.booking bb 
  WHERE bb.id = bookingId 
  FOR UPDATE;


  --****************************************
  --from boking cursor get booking record...
  --****************************************
  FETCH refCursorValue INTO bookingResult;

  --********************************
  --update a column on found booking....
  --********************************
  SELECT SYSDATE into todayAtNow FROM DUAL;  
  UPDATE qms_model.booking SET 
          call_time = todayAtNow
  WHERE id = bookingResult.id;


  /*
  after the fetch refCursorValue is not
  valid and the client can't use it!
  */ 

END;

Calling this procedure the booking is found and the field is updated,but at the end the cursor is not valid and i cant use it for other operation, in this example i use the cursor to log the id field

set serveroutput on format wrapped;
DECLARE
  REFCURSORVALUE SYS_REFCURSOR;
  BOOKINGID NUMBER;
  bookingResult QMS_MODEL.booking%ROWTYPE;
BEGIN
  BOOKINGID := 184000000084539;
  GET_BOOKING(
    REFCURSORVALUE,
    BOOKINGID
  );
  FETCH REFCURSORVALUE INTO bookingResult;
  DBMS_OUTPUT.PUT_LINE('>>>OUT , cursor fetc,id='|| bookingResult.id ); 
END;

I model the booking in java using an entity 

@Entity
@Table(name = "BOOKING", schema = "QMS_MODEL")
@NamedNativeQueries({    
    @NamedNativeQuery(name = "booking.callNext.Oracle",
    query = "call GET_BOOKING(?,:bookingId)",
    callable = true,
    resultClass = Booking.class)
})
public class Booking implements Serializable {
..
..
}

...and i get it by a NamedNativeQuery:

long bookingID=...some value
Query q = entityMng.createNamedQuery("booking.callNext.Oracle");
q.setParameter("bookingId", bookingID);
List results = q.getResultList();
if (results!=null && !results.isEmpty()) {
  Booking eBooking = (Booking) results.get(0);
  ..
  ..
  ..
  ..i want use some booking data here....
  ..but i can't because the cursor is closed
}

the only request for me are -select the booking and update it in the same transaction into a stored procedure -call the stored procedure from java and retrieve the updated booking in the form of a @Entity-Booking

thank you in advance.

Upvotes: 2

Views: 14335

Answers (2)

APC
APC

Reputation: 146239

A ref cursor is not like the scrollable cursor we find in front end languages. It is a pointer to a resultset. This means, we can read it once and then it is exhausted. It is not reusable.

"when a booking is selected i need to update the call_time to mark it as "selected".When a booking has a non-null call_time isn't selectable any more. I need to return the updated record to the java application so i need to return it as the first parameter of the procedure with OUT sys_refcursor type.

" Note that the real select could be hard so i don't want to execute it more than one time"

Okay, here is one approach. Caveat: this is proof of concept (i.e. untested code) and not guaranteed to work but it seems like a feasible solution.

CREATE OR REPLACE PROCEDURE GET_BOOKING  
 (      refCursorValue OUT SYS_REFCURSOR,     
        bookingId IN QMS_MODEL.booking.id%type   ) 
AS     
     rowids dbms_debug_vc2coll;
begin
     update  qms_model.booking bb  
     set     bb.call_time = sysdate
     where bb.id = bookingId 
     returning rowidtochar(rowid) bulk collect into rowids;

     open refCursorValue for
         select * 
         from qms_model.booking bbto
         where rowid in ( select chartorowid(column_value) from table(rowids));
end;
/

Basically:

  1. update the row(s) you want to select
  2. use the RETURNING clause to capture the rowids of the updated rows
  3. then open the refcursor using the rowids to select only the updated rows.

You do issue two queries but selecting using ROWID is pretty fast.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191285

The problem is at (3) - 'returns by an OUT parameter a SYS_REFCURSOR that points to the found record'. It doesn't point to that record because you've fetched past it. I'm assuming you're only expecting a single record with that ID anyway; if you have more than one then the returned cursor will point to the next record with that ID, but your update will have updated all matching records with that ID, not just the one you fetched.

If you only have a single record, why use a cursor? The only reason I can see is to allow you to use for update, but you aren't using the corresponding where current of in the update.

Upvotes: 1

Related Questions