Get Results from REFCURSOR with unknown number of columns in PLSQL

Back in 2012, I wrote a post on View results of REFCURSOR out parameter . That post somehow attracted a comment from Great Steven Feuerstein as "What happens if I do not know as I am writing my code how many columns I am fetching?". So with this post I am trying to answer the question: How to get results from REFCURSOR with unknown number of columns in PL/SQL. Let's try to code it out.

Here is a very simple procedure which takes a SQL Query as input and returns a REFCURSOR opened with that query.

SQL> CREATE OR REPLACE PROCEDURE MYPROC (P_SQL VARCHAR2, CR OUT SYS_REFCURSOR)
  2  IS
  3  BEGIN
  4   OPEN CR FOR P_SQL;
  5  END;
  6  /
Procedure created.

Now the problem is we do not know what will be the SQL passed in procedure, what are the number of columns and their data type. How to fetch data from this REFCURSOR? Oh, Yes, Oracle provides us a very helpful function DBMS_SQL.TO_CURSOR_NUMBER which converts a REFCURSOR variable to a SQL cursor number, which you can pass to DBMS_SQL subprograms. Wow we have power of DBMS_SQL to explore data of REFCURSOR.

Now Let's try to write a procedure which takes REFCURSOR as input and simply print it's data, without knowing any information about columns.

SQL> CREATE OR REPLACE PROCEDURE PRINT_REFCURSOR (CR IN OUT SYS_REFCURSOR)
  2  IS
  3      l_curid      NUMBER;
  4     l_col_cnt    INTEGER;
  5     rec_tab      DBMS_SQL.DESC_TAB;
  6     l_text       VARCHAR2 (4000);
  7     l_flag       NUMBER;
  8     l_varchar2   VARCHAR2 (4000);
  9     l_number     NUMBER;
 10     l_date       DATE;
 11
 12  BEGIN
 13     l_curid := DBMS_SQL.TO_CURSOR_NUMBER(CR);
 14
 15      -- define columns
 16      DBMS_SQL.DESCRIBE_COLUMNS (l_curid, l_col_cnt, rec_tab);
 17     FOR pos IN 1 .. l_col_cnt
 18     LOOP
 19        CASE rec_tab (pos).col_type
 20           WHEN 1 THEN
 21              DBMS_SQL.DEFINE_COLUMN (l_curid,pos,l_varchar2,2000);
 22           WHEN 2 THEN
 23              DBMS_SQL.DEFINE_COLUMN (l_curid, pos, l_number);
 24           WHEN 12 THEN
 25              DBMS_SQL.DEFINE_COLUMN (l_curid, pos, l_date);
 26           ELSE
 27              DBMS_SQL.DEFINE_COLUMN (l_curid,pos,l_varchar2,2000);
 28        END CASE;
 29     END LOOP;
 30
 31     -- Print column names of dynamic sql
 32     FOR pos IN 1 .. l_col_cnt
 33     LOOP
 34        l_text := LTRIM (l_text || ',' || LOWER (rec_tab (pos).col_name), ',');
 35     END LOOP;
 36
 37     DBMS_OUTPUT.PUT_LINE (l_text);
 38
 39     -- Print data fetched by query
 40     LOOP
 41        l_flag := DBMS_SQL.FETCH_ROWS (l_curid);
 42        EXIT WHEN l_flag = 0;
 43        l_text := NULL;
 44
 45        FOR pos IN 1 .. l_col_cnt
 46        LOOP
 47           CASE rec_tab(pos).col_type
 48              WHEN 1 THEN
 49                 DBMS_SQL.COLUMN_VALUE (l_curid, pos, l_varchar2);
 50                 l_text := LTRIM (l_text || ',"' || l_varchar2 || '"', ',');
 51              WHEN 2 THEN
 52                 DBMS_SQL.COLUMN_VALUE (l_curid, pos, l_number);
 53                 l_text := LTRIM (l_text || ',' || l_number, ',');
 54              WHEN 12 THEN
 55                 DBMS_SQL.COLUMN_VALUE (l_curid, pos, l_date);
 56                 l_text := LTRIM (l_text|| ','|| TO_CHAR (l_date, 'DD/MM/YYYY HH24:MI:SS'),',');
 57              ELSE
 58                 l_text := LTRIM (l_text || ',"' || l_varchar2 || '"', ',');
 59           END CASE;
 60        END LOOP;
 61        DBMS_OUTPUT.PUT_LINE (l_text);
 62     END LOOP;
 63
 64     DBMS_SQL.CLOSE_CURSOR (l_curid);
 65  END;
 66  /
Procedure created.

It compiled. Now we are good to test it. Let me try it first with a simple query.

SQL> set serveroutput on
SQL> declare
  2      C SYS_REFCURSOR;
  3  begin
  4     MYPROC('SELECT * FROM DEPT', C);
  5     PRINT_REFCURSOR(C);
  6  END;
  7  /

deptno,dname,loc
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

PL/SQL procedure successfully completed.

Great !!! Now it is time test our procedure with for some complex query with joins and some analytic function.

SQL> declare
  2    C SYS_REFCURSOR;
  3  begin
  4    MYPROC('SELECT EMPNO, ENAME, DNAME, SAL,
  5     RANK() OVER (PARTITION BY DNAME ORDER BY SAL DESC) SAL_RANK
  6      FROM EMP E, DEPT D
  7      WHERE E.DEPTNO = D.DEPTNO
  8     ORDER BY DNAME, SAL_RANK', C);
  9    PRINT_REFCURSOR(C);
 10  END;
 11  /

empno,ename,dname,sal,sal_rank
7839,"KING","ACCOUNTING",5000,1
7782,"CLARK","ACCOUNTING",2450,2
7934,"MILLER","ACCOUNTING",1300,3
7902,"FORD","RESEARCH",3000,1
7788,"SCOTT","RESEARCH",3000,1
7566,"JONES","RESEARCH",2975,3
7876,"ADAMS","RESEARCH",1100,4
7369,"SMITH","RESEARCH",800,5
7698,"BLAKE","SALES",2850,1
7499,"ALLEN","SALES",1600,2
7844,"TURNER","SALES",1500,3
7521,"WARD","SALES",1250,4
7654,"MARTIN","SALES",1250,4
7900,"JAMES","SALES",950,6

PL/SQL procedure successfully completed.

WOW !!! Our procedure PRINT_REFCURSOR is working as expected. DBMS_SQL.TO_CURSOR_NUMBER was the savior and power of DBMS_SQL is awesome. I hope you all have enjoyed reading this article. Feedback are well appreciated.

Related Links:
- Difference Between Cursor And Ref Cursor
- Ref Cursor: Strongly Typed VS Weakly Typed
- Cursor with Parameters in Oracle
- View results of REFCURSOR out parameter
- PLSQL Tuning: Bulk Collect with Dynamic SQL