Oracle Cursor examples

-- Write a cursor:
 
FUNCTION write_cursor RETURN sys_refcursor
IS
  return_cursor sys_refcursor;
BEGIN
  OPEN return_cursor FOR
    SELECT * FROM dual; -- Your SQL here!
  RETURN return_cursor;
END write_cursor;
 
 
-- Write cursor with parameter:
 
CREATE OR REPLACE
FUNCTION write_cursor(filter_ parameter IN dual.dummy%TYPE DEFAULT 'X') RETURN sys_refcursor
IS
  return_cursor sys_refcursor;
BEGIN
  OPEN return_cursor FOR
    SELECT * FROM dual WHERE dummy = filter_ parameter; -- Your SQL here!
  RETURN return_cursor;
END write_cursor;
 
 
-- Read a Cursor:
 
PROCEDURE read_cursor(cursor_in IN sys_refcursor)
IS
  l_rec dual%ROWTYPE;
BEGIN
  LOOP
    FETCH cursor_in INTO l_rec;
    EXIT WHEN cursor%notfound;
  END LOOP;  
END read_cursor;
 
 
-- Testing / Printing:
 
-- Quick and dirty method:
 
SELECT get_employees() FROM dual; 
 
 
-- Nice and neat method:
 
SET timing ON
var ref_cursor refcursor;
BEGIN
  my_package.my_proc(:ref_cursor);
END;
/
print ref_cursor;