title: read data from SYS_REFCURSOR in a Oracle stored procedure and reuse it in java
body: <p>I have this table:</p>
  
  <pre class="lang-sql prettyprint-override"><code>  CREATE TABLE "QMS_MODEL"."BOOKING" (
         "ID" NUMBER ( 19, 0 ) CONSTRAINT "QMS_BOOKING_NN_1" NOT NULL ENABLE
        ,"CALL_TIME" TIMESTAMP ( 6 )
    );
  </code></pre>
  
  <p>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 :</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>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</p>
  
  <pre class="lang-sql prettyprint-override"><code>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('&gt;&gt;&gt;OUT , cursor fetc,id='|| bookingResult.id ); 
  END;
  </code></pre>
  
  <p>I model the booking in java using an entity </p>
  
  <pre><code>@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 {
  ..
  ..
  }
  </code></pre>
  
  <p>...and i get it by a NamedNativeQuery:</p>
  
  <pre><code>long bookingID=...some value
  Query q = entityMng.createNamedQuery("booking.callNext.Oracle");
  q.setParameter("bookingId", bookingID);
  List results = q.getResultList();
  if (results!=null &amp;&amp; !results.isEmpty()) {
    Booking eBooking = (Booking) results.get(0);
    ..
    ..
    ..
    ..i want use some booking data here....
    ..but i can't because the cursor is closed
  }
  </code></pre>
  
  <p>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</p>
  
  <p>thank you in advance.</p>