Create and test a function that returns a cursor.

Interview Question: How to create and test a function that returns a cursor?

Level: Beginner/Intermediate

Step 1: Create a sample function

All you need to know to complete this step is how to use SYS_REFCURSOR type:

SQL> CREATE OR REPLACE FUNCTION get_emp_data RETURN SYS_REFCURSOR
  2  AS
  3     c SYS_REFCURSOR;
  4  BEGIN
  5     OPEN c FOR
  6     SELECT empno,ename,job,deptno
  7     FROM emp;
  8
  9     RETURN c;
 10  END;
 11  /

Function created.

Step 2: Test the new function
I will demonstrate 3 methods/workarounds for testing the function that has just been created.

Method/Workaround #1: Using Anonymous Block
Assumption: We know the structure of the returning cursor, i.e fields and their data types.

set serveroutput on size 2000 format wrapped

DECLARE
  c_emp SYS_REFCURSOR;
  TYPE typ_emp IS RECORD
   (
      empno  emp.empno%TYPE,
      ename  emp.ename%TYPE,
      job    emp.job%TYPE,
      deptno emp.deptno%TYPE
   );
   v typ_emp;
BEGIN
   c_emp:=get_emp_data;
   --Print the header line:
   DBMS_OUTPUT.PUT_LINE(LPAD('empno', 10) || '  ' ||
                        RPAD('ename', 15) ||
                        RPAD('job', 15) ||
                        LPAD('deptno', 6)
                        );
   DBMS_OUTPUT.PUT_LINE(LPAD('------', 10) || '  ' ||
                        RPAD('------', 15) ||
                        RPAD('------', 15) ||
                        LPAD('------', 6)
                        );
   LOOP
       FETCH c_emp INTO v;
       EXIT WHEN c_emp%NOTFOUND;
       --Print the current employee record:
       DBMS_OUTPUT.PUT_LINE(LPAD(v.empno, 10) || '  ' ||
                            RPAD(v.ename, 15) ||
                            RPAD(v.job, 15) ||
                            LPAD(v.deptno, 6)
                           );
   END LOOP;
   CLOSE c_emp;
END;
/
     empno  ename          job            deptno
    ------  ------         ------         ------
      7369  SMITH          CLERK              20
      7499  ALLEN          SALESMAN           30
      7521  WARD           SALESMAN           30
      7566  JONES          MANAGER            20
      7654  MARTIN         SALESMAN           30
      7698  BLAKE          MANAGER            30
      7782  CLARK          MANAGER            10
      7788  SCOTT          ANALYST            20
      7839  KING           PRESIDENT          10
      7844  TURNER         SALESMAN           30
      7876  ADAMS          CLERK              20
      7900  JAMES          CLERK              30
      7902  FORD           ANALYST            20
      7934  MILLER         CLERK              10

PL/SQL procedure successfully completed.

Method/Workaround #2: Using Select Statement
This method does not require us to know the structure of the cursor returned by the function:

SQL> SELECT get_emp_data
  2  FROM dual;

GET_EMP_DATA
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

Method/Workaround #3: Using Bind Variable and PRINT command in SQL*PLus
The following method does not require us to know the structure of the cursor returned by the function, but it can only be executed in SQL*Plus:

SQL> var c refcursor
SQL> exec :c:=get_emp_data

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.