oracle out ref cursor to a loop
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
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;
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.