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

22 comments:

  1. Very helpful article. Will save a lot of time while working with Refcursors

    ReplyDelete
  2. DBMS_SQL.TO_CURSOR_NUMBER is very good. Very good explanation.

    ReplyDelete
  3. good one and very useful

    ReplyDelete
  4. Thanks for the simple and easy to read explanation.

    ReplyDelete
  5. Great post now we feel the power of PLSQL !

    ReplyDelete
  6. Thank you for an interesting example

    ReplyDelete
  7. Good Job. Congrats

    ReplyDelete
  8. Kanakaraj PeriasamyFebruary 14, 2016 at 3:44 AM

    Very Helpful, Thanks.

    ReplyDelete
  9. Great to know how to do this. Good Job and thanks for sharing!

    ReplyDelete
  10. I like a lot.... thanks

    ReplyDelete
  11. Thanks for sharing, very useful

    ReplyDelete
  12. great to know how to do this, thanks

    ReplyDelete
  13. Is there possible pass only table name as a parementer and get all column values?

    ReplyDelete
    Replies
    1. this should work

      CREATE OR REPLACE PROCEDURE MYPROC (P_TABLE_NAME VARCHAR2, CR OUT SYS_REFCURSOR)
      IS
      BEGIN
      OPEN CR FOR 'select * from ' || P_TABLE_NAME;
      END;
      /

      Delete
  14. it is very useful for developers..awesome..!!

    ReplyDelete
  15. Good one buddy.

    ReplyDelete
  16. Can you just explain why only when 1,2 and 12 ??

    ReplyDelete
  17. very nice, very impressive... you did a great job done :)
    best regards

    ReplyDelete