oracle out ref cursor to a loop

oracle out ref cursor to a loop

ref cursor for loop in oracle example
oracle function return ref cursor example
parameterized ref cursor in oracle
how to print ref cursor output in oracle
ref cursor in oracle example techonthenet
how to print sys_refcursor in oracle
strong ref cursor example
oracle function return cursor

How to pass the 'values' of the output cursor o_cur to a further loop?

CREATE OR REPLACE PROCEDURE dyn_cursor (o_cur OUT SYS_REFCURSOR)
IS
    script   VARCHAR2 (4000);
BEGIN
    script := 'select sysdate-1 notnow, sysdate today from dual union all select sysdate+1 notnow, sysdate today from dual';
    OPEN o_cur FOR script;

-- the question is related to this block:   
    for i in o_cur
    loop
        DBMS_OUTPUT.PUT_LINE(i.notnow);
    end loop;
-----------------------

END;

FETCH is usually handled outside the procedure when the refcursor is an OUT variable

CREATE OR REPLACE PROCEDURE dyn_cursor (o_cur OUT SYS_REFCURSOR)
IS
    script   VARCHAR2 (4000);
BEGIN
    script := 'select sysdate-1 notnow, sysdate today from dual union all select sysdate+1 notnow, sysdate today from dual';
    OPEN o_cur FOR script;

END;
/

SET SERVEROUTPUT ON
DECLARE
     v_cur      SYS_REFCURSOR;
     v_notnow   DATE;
     v_today    DATE;
BEGIN
     dyn_cursor(v_cur);
     LOOP
          FETCH v_cur INTO
               v_notnow,
               v_today;
          EXIT WHEN v_cur%notfound;
          dbms_output.put_line(v_notnow);
     END LOOP;
END;
/


02-10-18
04-10-18


PL/SQL procedure successfully completed.

PL/SQL Cursor Variables with REF CURSOR, With a cursor variable, you simply pass the reference to that cursor. process each employee LOOP FETCH c_direct_reports INTO l_employee_id, l_first_name,  1 Answer 1. A ref cursor being a pointer to an open cursor used to send an open cursor as an out argument to the client app to loop through the record.


This seems a strange construct. I would create a view you can reuse at different places.

CREATE OR REPLACE VIEW VW_Times AS
    SELECT sysdate-1 notnow, sysdate today FROM dual
    UNION ALL
    SELECT sysdate+1 notnow, sysdate today FROM dual;

and

CREATE OR REPLACE PROCEDURE OutputTime ()
IS
   CURSOR o_cur IS
     SELECT * FROM VW_Times;

BEGIN
   FOR i IN o_cur
   LOOP
      DBMS_OUTPUT.PUT_LINE(i.notnow);
   END LOOP;
END;

oracle out ref cursor to a loop, FETCH is usually handled outside the procedure when the refcursor is an OUT variable. CREATE OR REPLACE PROCEDURE dyn_cursor  In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSORs are represented through the OracleRefCursor ODP.NET class. REF CURSORs have the following characteristics: A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR in order to access it.


Tanks to post of Kaushik Nayak, I found the acceptable solution:

CREATE OR REPLACE PROCEDURE dyn_cursor (o_cur OUT SYS_REFCURSOR)
IS
    script   VARCHAR2 (4000);
    type array is table of VARCHAR2(20) index by binary_integer;
    l_datapoint1 array;
    l_datapoint2 array;
BEGIN
    script := 'select sysdate-1 notnow, sysdate today from dual union all select sysdate+1 notnow, sysdate today from dual';
    OPEN o_cur FOR script;

    LOOP
        FETCH o_cur bulk collect into l_datapoint1, l_datapoint2;
          for i in 1 .. l_datapoint1.count
          loop
              DBMS_OUTPUT.PUT_LINE( l_datapoint1(i) || ', ' || l_datapoint2(i) );
          end loop;
          exit when o_cur%notfound;
      end loop;
      close o_cur;
END;

PL/SQL 101 : Understanding Ref Cursors, As such it went into a loop, fetching each row of data from the database, using the ref cursor as it's reference (pointer) to the query, and  The cursor FOR LOOP implicitly creates its loop index as a record variable with the row type in which the cursor returns and then opens the cursor. In each loop iteration, the cursor FOR LOOP statement fetches a row from the result set into its loop index. If there is no row to fetch, the cursor FOR LOOP closes the cursor.


Ask TOM "SYS_REF_CURSOR AND REF CURSOR", When i learning about Cursor Variable (Ref Cursor) on One Website I found this v VARCHAR2(1); BEGIN c := f(); -- Get ref cursor from function LOOP or replace 2 procedure P(r out sys_refcursor, s sys.odcinumberlist) is 3  The most important benefit of a cursor variable is that it enables passing the result of a query between PL/SQL programs. Without a cursor variable, you have to fetch all data from a cursor, store it in a variable e.g., a collection, and pass this variable as an argument. With a cursor variable, you simply pass the reference to that cursor.


Looping through Ref Cursor, All of your suggestions have worked out perfectly. I just need clarification on one point.. Can you please explain why I am I doing two loops  Answer: Here are some working examples of ref cursors. PL/SQL Ref Cursors examples. A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function.


SYS_REFCURSOR and FETCH with multi-table - Ask Tom, We have a stored procedure with an output reference cursor I put a loop to print out the column names, so I can see the structure is correct. I will take out one sentence REF cursors are always explicit cursors (so on the other side: cursor for loops are always implicit cursors) So: you close the cursor before you even FETCH from it AND you do that using a cursor for loop. Also: you use dynamic sql where there is no need to use dynamic sql AND cursor for loops are unscalable anyway.