REF Cursor in Oracle

Introduction to REF CURSOR:
---------------------------
A REF CURSOR is basically a data type.  A variable created based on such a data type is
generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.
The primary advantage of using cursor variables is their capability to pass result sets
between sub programs (like stored procedures, functions, packages etc.).

Let us start with a small sub-program as follows:

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  en emp.ename%type;
begin
  open c_emp for select ename from emp;
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
  close c_emp;
end;


%ROWTYPE with REF CURSOR
------------------------

In the previous section, I retrieved only one column (ename) of information using REF CURSOR.
 Now I would like to retrieve more than one column (or entire row) of information using the same.
  Let us consider the following example:

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  er emp%rowtype;
begin
  open c_emp for select * from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.ename || ' - ' || er.sal);
  end loop;
  close c_emp;
end;

Ref cursors have the following characteristics:
----------------------------------------------

A ref cursor is not updatable. The result set represented by the ref cursor is read-only.
 You cannot update the database by using a ref cursor.

A ref cursor is a PL/SQL datatype. You create and return a ref cursor inside a PL/SQL code block.



Passing Ref Cursor  as Parameter to Procedure:
====================================

CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS

TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;

rec_array array_t;

BEGIN
  FETCH p_cursor BULK COLLECT INTO rec_array;

  FOR i IN rec_array.FIRST .. rec_array.LAST
  LOOP
    dbms_output.put_line(rec_array(i));
  END LOOP;
END pass_ref_cur;


Execution
-------------

set serveroutput on

DECLARE
 rec_array SYS_REFCURSOR;
BEGIN
  OPEN rec_array FOR
  'SELECT empname FROM employees';

  pass_ref_cur(rec_array);
  CLOSE rec_array;
END;
/

Example:2
========

CREATE OR REPLACE PROCEDURE child (
 p_NumRecs IN PLS_INTEGER,
 p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
  OPEN p_return_cur FOR
  'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
END child;


CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS
 p_retcur  SYS_REFCURSOR;
 at_rec    all_tables%ROWTYPE;
BEGIN
  child(pNumRecs, p_retcur);

  FOR i IN 1 .. pNumRecs
  LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.table_name ||
    ' - ' || at_rec.tablespace_name ||
    ' - ' || TO_CHAR(at_rec.initial_extent) ||
    ' - ' || TO_CHAR(at_rec.next_extent));
  END LOOP;
END parent;
/

===============================
The main advantage of ref cursor is

1. We can  return result set to a client which we can't do using normal cursors.
2. It can be passed as parameter to sub programs.



Ur's
AmarAlam