PL/SQL 101 : Understanding Ref Cursors

Version 5

    PL/SQL 101: Understanding Ref Cursors

    (update from the original thread: PL/SQL 101 : Understanding Ref Cursors)

     

    Author: BluShadow

    Last Updated: 22nd May 2015

     

     

    Introduction

     

    Often on the community we get questions along the lines of:


    "I'm passing a ref cursor back from a function, how do I use the data in that ref cursor in my select statement/query"

     

    or

     

    "How can I tell how many rows are in my ref cursor"

     

    Equally as often, the problem comes from a lack of understanding what a ref cursor actually is.  So I thought I'd describe the basics behind ref cursors with a view to giving a clearer understanding of them.

     

    All of the below examples are done through the command line of SQL*Plus.  If you're using a different tool, some of the commands may differ and you should  refer to the documentation for that product (or just learn to use SQL*Plus as it's the Best! ).  Also, I've provided the main code without the SQL*Plus prompt or line numbers so it can just be copied and pasted easily.

     

    1. What is a ref cursor and how is the data fetched

     

    Let's start with a simple function that opens a ref cursor and returns it. (We'll just select some employee details for a supplied department)

     

    create or replace function get_dept_emps(p_deptno in number) return sys_refcursor is
      v_rc sys_refcursor;
    begin
      open v_rc for 'select empno, ename, mgr, sal from emp where deptno = :deptno' using p_deptno;
      return v_rc;
    end;
    /

     

    Function created.

     

    Now, if we look at using this through SQL*Plus we first create ourselves a ref cursor variable to accept the results of the function (this is the equivalent of the PL/SQL sys_refcursor datatype), and then call the function to get the ref cursor back..

     

    SQL> var rc refcursor
    SQL> exec :rc := get_dept_emps(10);

     

    PL/SQL procedure successfully completed.

     

    Ok, so our variable "rc" has our ref cursor.

     

    If we use the SQL*Plus 'print' command on our ref cursor we get..

     

    SQL> print rc;

         EMPNO ENAME             MGR        SAL
    ---------- ---------- ---------- ----------
          7782 CLARK            7839       2450
          7839 KING                        5000
          7934 MILLER           7782       1300


    Brilliant, so our ref cursor has provided the rows we wanted from the employee table.

     

    I'm so pleased with that, I want to do it again..

     

    SQL> print rc;
    SP2-0625: Error printing variable "rc"

     

    Uh oh! What's happened here?  Why can't we print the data that's in our ref cursor again?

     

    A common mistake is that people believe that a ref cursor actually contains the result data from the query.  The truth is that the ref cursor doesn't contain any data at all, it's just a pointer to the query.

     

    So why did the first print statement print out the results?

     

    SQL*Plus looked at the ref cursor and saw that it was an open cursor.  As such it went into a loop, fetching each row of data from the database, using the ref cursor as it's reference (pointer) to the query, and displaying each row of data until it had no more rows to fetch.  Once it's fetched all the rows it closes the cursor.  The power of SQL*Plus's "print" command eh! 


    When we tried to print the ref cursor a second time, we got an error because SQL*Plus looked at the cursor, saw it was not an open cursor (the previous print call had closed it when it had finished) and couldn't perform the task of printing anything.

     

    Let's look at this in a bit more detail so it's a little clearer.  We'll use some PL/SQL code to break down the processing that SQL*Plus was doing internally...

     

    Firstly, let's get the open cursor and ask it how many rows it has..

     

    declare
      v_rc    sys_refcursor;
    begin
      v_rc := get_dept_emps(10);  -- This returns an open cursor
      dbms_output.put_line('Rows: '||v_rc%ROWCOUNT);
      close v_rc;
    end;
    /
    Rows: 0

     

    PL/SQL procedure successfully completed.

     

    Yep, sure enough it's reporting 0 rows.

     

    It's important that we remember that ROWCOUNT reports how many rows have been fetched through a cursor.  Just after opening the cursor we haven't fetched any rows yet.  If we fetch a row of data then we can see this change..

     

    declare
      v_rc    sys_refcursor;
      v_empno number;
      v_ename varchar2(10);
      v_mgr   number;
      v_sal   number;
    begin
      v_rc := get_dept_emps(10);  -- This returns an open cursor
      dbms_output.put_line('Pre Fetch: Rows: '||v_rc%ROWCOUNT);
      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
      dbms_output.put_line('Post Fetch: Rows: '||v_rc%ROWCOUNT);
      close v_rc;
    end;
    /
    Pre Fetch: Rows: 0
    Post Fetch: Rows: 1

     

    PL/SQL procedure successfully completed.

     

    Ok, so clearly the fetch has changed the row count.  That proves it.

    Let's fetch all our data and display it..

     

    declare
      v_rc    sys_refcursor;
      v_empno number;
      v_ename varchar2(10);
      v_mgr   number;
      v_sal   number;
    begin
      v_rc := get_dept_emps(10);  -- This returns an open cursor
      loop
        fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
        exit when v_rc%NOTFOUND;  -- Exit the loop when we've run out of data
        dbms_output.put_line('Row: '||v_rc%ROWCOUNT||' # '||v_empno||','||v_ename||','||v_mgr||','||v_sal);
      end loop;
      close v_rc;
    end;
    /
    Row: 1 # 7782,CLARK,7839,2450
    Row: 2 # 7839,KING,,5000
    Row: 3 # 7934,MILLER,7782,1300

     

    PL/SQL procedure successfully completed.

     

    And what happens if we try and fetch more data after it's finished, just like we tried to do in SQL*Plus...

     

    declare
      v_rc    sys_refcursor;
      v_empno number;
      v_ename varchar2(10);
      v_mgr   number;
      v_sal   number;
    begin
      v_rc := get_dept_emps(10);  -- This returns an open cursor
      loop
        fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
        exit when v_rc%NOTFOUND;  -- Exit the loop when we've run out of data
        dbms_output.put_line('Row: '||v_rc%ROWCOUNT||' # '||v_empno||','||v_ename||','||v_mgr||','||v_sal);
      end loop;
      close v_rc;
      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
    end;
    /
    Row: 1 # 7782,CLARK,7839,2450
    Row: 2 # 7839,KING,,5000
    Row: 3 # 7934,MILLER,7782,1300
    declare
    *
    ERROR at line 1:
    ORA-01001: invalid cursor
    ORA-06512: at line 15

     

    As expected we get an error.  In SQL*Plus it captured that error and printed it's own 'friendly' response... 'Error printing variable "rc"'

     

    So now we understand the basics of what a ref cursor is.

    You can clearly see that it is just a pointer to the query and it doesn't contain any data itself, it just allows us to reference the query so that we can fetch data as we require it.


    2. How can we use a ref cursor in a SQL query?

     

    Ok, so now you've got your ref cursor you want to use it.  But how can you fetch the data from the ref cursor inside another SQL Select statement?

     

    Perhaps we can just select from it?

     

    SQL> select * from get_dept_emps(10);
    select * from get_dept_emps(10)
                               *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended

     

    Nope.  How about if we tell SQL to treat it as a table?

     

    SQL> select * from table(get_dept_emps(10));
    select * from table(get_dept_emps(10))
                  *
    ERROR at line 1:
    ORA-22905: cannot access rows from a non-nested table item

     

    What about using it as a set of data in an IN condition?

    SQL> select * from emp where empno in (get_dept_emps(10));
    select * from emp where empno in (get_dept_emps(10))
                                      *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected - got CURSER

     

    (N.B. The spelling error of "CURSER" is Oracle's, not mine. )
    (Ok, I know the ref cursor is referencing more than just the empno in that example, but it would still result in the same error if it just referenced the empno)

     

    The problem we're having is because the ref cursor isn't a table of data either in the literal database sense or in an array/collection sense, and it's not a set of data that can be compared with the IN clause.

     

    So what's the point in these ref cursors? Is there a way we can use them?

     

    Yes there is..

     

    First let's create a type structure on the database.  Remember, SQL cannot access PL/SQL table structures (certainly prior to 12c) so the type must be a database object..

     

    create or replace type emptype as object(empno number,
                                             ename varchar2(10),
                                             mgr   number,
                                             sal   number);
    /

     

    Type created.

     

    create or replace type t_emptype as table of emptype;
    /

     

    Type created.

     

    Ok, so we have a structure to hold a record and a type that is a table of that structure.  So far so good.  But in order to populate that structure with data coming from the ref cursor we can't just select from it as we saw above.  Instead we need to provide some PL/SQL to actually do the fetching of data for us and populate the structure...

     

    create or replace function populate_emps(deptno in number := null) return t_emptype is
      v_emptype t_emptype := t_emptype();  -- Declare a local table structure and initialize it
      v_cnt     number := 0;
      v_rc      sys_refcursor;
      v_empno   number;
      v_ename   varchar2(10);
      v_mgr     number;
      v_sal     number;
    begin
      v_rc := get_dept_emps(deptno);
      loop
        fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
        exit when v_rc%NOTFOUND;
        v_emptype.extend;
        v_cnt := v_cnt + 1;
        v_emptype(v_cnt) := emptype(v_empno, v_ename, v_mgr, v_sal);
      end loop;
      close v_rc;
      return v_emptype;
    end;
    /

     

    Function created.

     

    The above function calls the function that opens the ref cursor, then loops through, fetching each row and populating our SQL type structure.  When all rows have been fetched, the ref cursor is closed and the SQL table type structure is passed back from the function.


    So now we have something in an structure that SQL understands, we should be able to query directly from it..

     

    select * from table(populate_emps(30));

     

         EMPNO ENAME             MGR        SAL
    ---------- ---------- ---------- ----------
          7499 ALLEN            7698       1600
          7521 WARD             7698       1250
          7654 MARTIN           7698       1250
          7698 BLAKE            7839       2850
          7844 TURNER           7698       1500
          7900 JAMES            7698        950

     

    6 rows selected.

     

    select * from emp where empno in (select empno from table(populate_emps(30)));

     

         EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
          7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
          7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30
          7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
          7900 JAMES      CLERK           7698 03-DEC-1981        950                    30

     

    6 rows selected.

     

    Hoorah!

     

    We've successfully taken our ref cursor (pointer) and used it to fetch the data back that we want in a structure that SQL can understand.  Ok, the examples are pretty meaningless as they stand as we could easily have achieved the same results through a basic select, but the method is what is important to understand here.


    3. What is the point of ref cursors?

     

    A good question.  Many people learn about ref cursors and then try and use them everywhere.  Although I personally know how to write and use ref cursors, I've found very little use for them in my production code. If you are thinking of using a ref cursor, the first thing you should ask yourself is "Why do I need one?"


    If your answer is that you need to write your code to dynamically generate SQL, then perhaps you should look at what you are trying to achieve.  Typically dynamic SQL, in a lot of cases, is unnecessary and a major cause of unmaintainable and unscalable code as well as possible performance issues and SQL injection; not to mention that a well designed database would mean that you should already know your tables and columns and have no need for anything to be dynamic.


    If you really have a valid need to dynamically create SQL and you fully understand the implications and risks involved, then a ref cursor is useful for this task.  What you will find however is that you are limited with ref cursors to a fixed result structure, so it may not be as generic a solution as you had initially planned for.  If you consider the examples from above, you will see that we had to define a SQL object/structure of known columns in order to receive the data from the ref cursor.  So, whilst the function that opens the ref cursor could do so for any piece of query text, the only way of successfully getting the data out of that ref cursor is to know what columns we are expecting back from it.


    A common reason we see on the community is people who are trying to take an unknown number of rows and pivot that data into a dynamically unknown number of columns.  You may think that's a good reason to use dynamic queries, but first consider that, if you're trying to do this, you're requirement is to try and display the data in a user friendly way, and SQL isn't necessarily the right tool for this; the right tool being reporting tools, most of which can already take data from a standard SQL statement and pivot it dynamically for you.

     

    There is, however, a way around the issue of not knowing column names because of a dynamic query.

    Either use the DBMS_SQL package to dynamically generate and process your queries or, from 11g onwards, take your ref cursor and convert it to a DBMS_SQL cursor using 11g's new DBMS_SQL.TO_CURSOR_NUMBER feature.

    DBMS_SQL is a very powerful way of writing dynamic SQL, and I'll demonstrate that in the section below.

     

    Ref cursors are typically consumed by 3rd party interfaces, such as Java or .NET etc. which can describe the cursor to determine it's columns and datatypes. This can be done in PL/SQL (from 11g onwards) using DBMS_SQL...but there is rarely a good reason for doing it.

     

    4. Processing a ref cursor dynamically (Advanced topic)

     

    For completeness I'm going to demonstrate the processing of a ref cursor (though remember, you'd have to have a really good reason to want to do this).


    In SQL*Plus, the interface hides the fact that, when it gets a ref cursor returned and you issue a 'print' command, it internally obtains a description of the ref cursor so that it can determine what columns there are, what they're called and what their datatypes are.  It needs that information so that it can format the output it gives you.


    This is similar in other 3rd party interfaces, whether it's Java, .NET or something else that supports ref cursors.  They get the ref cursor returned as a pointer and can then obtain a description of it to know what it's going to provide.


    However, in PL/SQL's native cursor processing, we cannot do this.  When you issue a FETCH statement in PL/SQL, you are expected to fetch the data INTO a variable or structure that is appropriate for holding the results.  To do that, you need to know the structure at the time you compile the code so that you can declare the appropriate variables or types.

     

    As mentioned in the above section, from 11g onwards, Oracle does provide an additional function in the DBMS_SQL package called DBMS_SQL.TO_CURSOR_NUMBER.  This function takes a ref cursor (remember it's just a pointer) and casts it to a DBMS_SQL cursor (which is another kind of pointer, actually just a number that references the cursor).  Once it's a DBMS_SQL cursor, we have all the functionality that DBMS_SQL provides, which includes being able to DESCRIBE the cursor to find out it's columns and datatypes and to fetch the data based on column position rather than name (as we potentially don't know the names if it's dynamic!)...

     

    What we're going to do here then, is to emulate (in a basic sense) what happens behind the scenes of the SQL*Plus 'print' command and other similar interfaces.

     

    Let's start with a procedure that accepts a ref cursor as a parameter, converts it to a DBMS_SQL cursor, and then DESCRIBE's what the cursor results will look like:

     

    create or replace procedure process_ref_cur(p_rc in out sys_refcursor) as
      dbms_cur    integer;
      rec_tab     dbms_sql.desc_tab2;
      col_cnt     integer;
    begin
      -- Convert the incoming Ref Cursor to a DBMS_SQL cursor
      dbms_cur := dbms_sql.to_cursor_number(p_rc);

      -- Get the description of the cursor projection.
      -- This tells us the number of columns and a structured
      -- datatype containing information about each column.
      dbms_sql.describe_columns2(dbms_cur, col_cnt, rec_tab);

     

      -- 'print' the description
      for j in 1..col_cnt
      loop
        dbms_output.put_line('Column: '||to_char(j)||' : '||
                             case rec_tab(j).col_type
                               when 1 then 'VARCHAR2' -- or NVARCHAR2
                               when 2 then 'NUMBER'
                               when 12 then 'DATE'
                               when 96 then 'CHAR'    -- or NCHAR
                             else 'OTHER DATATYPE'
                             end||
                             case when rec_tab(j).col_type in (1,96) then '('||rec_tab(j).col_max_len||')' else null end||' : '||
                             rec_tab(j).col_name
                            );
      end loop;

      -- finally, ensure we close the cursor to release resources
      dbms_sql.close_cursor(dbms_cur);
    end;
    /

     

    Note: the data types are internal data type numbers (see http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci03typ.htm#LNOCI16268)
    To keep the example simple, I've just included the most common datatypes, but it's easy enough to add more as required.

     

    In the background, I've updated my get_dept_emps function to also include the hiredate from the emp table, so we have a DATE datatype to see (you should be able to figure that out without me showing it)


    In SQL*Plus, declare a ref cursor variable (like we did at the very beginning) and obtain our ref cursor...

     

    SQL> var rc refcursor
    SQL> exec :rc := get_dept_emps(10);

     

    PL/SQL procedure successfully completed.


    Now call our new procedure passing it the ref cursor...

     

    SQL> exec process_ref_cur(:rc);
    Column: 1 : NUMBER : EMPNO
    Column: 2 : VARCHAR2(10) : ENAME
    Column: 3 : NUMBER : MGR
    Column: 4 : NUMBER : SAL
    Column: 5 : DATE : HIREDATE

     

    PL/SQL procedure successfully completed.


    Well look at that! We've managed to get details of the columns in our ref cursor.


    How about fetching the data too?  No problem...

     

    create or replace procedure process_ref_cur(p_rc in out sys_refcursor) as
      dbms_cur    integer;
      rec_tab     dbms_sql.desc_tab2;
      col_cnt     integer;
      -- local variables for fetching data into
      fvarchar    varchar2(4000);
      fnumber     number;
      fdate       date;
      --
      ret         number;
    begin
      -- Convert the incoming Ref Cursor to a DBMS_SQL cursor
      dbms_cur := dbms_sql.to_cursor_number(p_rc);

      -- Get the description of the cursor projection.
      -- This tells us the number of columns and a structured
      -- datatype containing information about each column.
      dbms_sql.describe_columns2(dbms_cur, col_cnt, rec_tab);


      -- 'print' the description
      for j in 1..col_cnt
      loop
        dbms_output.put_line('Column: '||to_char(j)||' : '||
                             case rec_tab(j).col_type
                               when 1 then 'VARCHAR2' -- or NVARCHAR2
                               when 2 then 'NUMBER'
                               when 12 then 'DATE'
                               when 96 then 'CHAR'    -- or NCHAR
                             else 'OTHER DATATYPE'
                             end||
                             case when rec_tab(j).col_type in (1,96) then '('||rec_tab(j).col_max_len||')' else null end||' : '||
                             rec_tab(j).col_name
                            );
      end loop;


      -- Fetching the data

      -- Define each of the columns
      -- This may seem pointless as DBMS_SQL already knows the datatypes of each column, but we may, for example
      -- want to fetch all columns back into VARCHAR2 datatypes rather than the datatype of the column
      for j in 1..col_cnt
      loop
        -- for each column based on the datatype specify the local variable we will be fetching it into
        case rec_tab(j).col_type
          when 1 then dbms_sql.define_column(dbms_cur, j, fvarchar, rec_tab(j).col_max_len);
          when 2 then dbms_sql.define_column(dbms_cur, j, fnumber);
          when 12 then dbms_sql.define_column(dbms_cur, j, fdate);
          when 96 then dbms_sql.define_column(dbms_cur, j, fvarchar, rec_tab(j).col_max_len); -- we'll treat char as varchar2
        else -- assume any other datatype we will be able to implicitly convert to varchar2
          dbms_sql.define_column(dbms_cur, j, fvarchar, 4000);
        end case;
      end loop;
     
      -- Fetch each row in a loop, until no row is fetched
      loop
        -- Tell the cursor to fetch the next row, and return the number of rows fetched
        ret := DBMS_SQL.FETCH_ROWS(dbms_cur);
        -- if no row was fetched, exit our loop as we've reached the end
        exit WHEN ret = 0;

        -- now read the column value into the variable we defined for that column
        -- and print out the data
        for j in 1..col_cnt
        loop
          case rec_tab(j).col_type
            when 1 then
              dbms_sql.column_value(dbms_cur, j, fvarchar);
              dbms_output.put_line(fvarchar);
            when 2 then
              dbms_sql.column_value(dbms_cur, j, fnumber);
              dbms_output.put_line(to_char(fnumber));
            when 12 then
              dbms_sql.column_value(dbms_cur, j, fdate);
              dbms_output.put_line(to_char(fdate,'DD/MM/YYYY HH24:MI:SS'));
            when 96 then
              dbms_sql.column_value(dbms_cur, j, fvarchar);
              dbms_output.put_line(fvarchar);
          else
            dbms_sql.column_value(dbms_cur, j, fvarchar);
            dbms_output.put_line(fvarchar);
          end case;
        end loop;
      end loop;

      -- finally, ensure we close the cursor to release resources
      dbms_sql.close_cursor(dbms_cur);
    end;
    /

     

    Note: For more technical details on why we need to "Define" columns before we fetch them, see the Oracle Call Interface Programmer's Guide in the documentation (http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci05bnd.htm#i421494)

    quote:

    Query statements return data from the database to your application. When processing a query, you must define an output variable or an array of output variables for each item in the select-list for retrieving data. The define step creates an association that determines where returned results are stored, and in what format.
    ...
    OCI processes the define call locally on the client side. In addition to indicating the location of buffers where results should be stored, the define step determines what data conversions must take place when data is returned to the application.

     

    Ok, so the code is starting to look a little more complicated as we're having to process each column of the data separately.  Let's see it in action...

     

    SQL> var rc refcursor
    SQL> exec :rc := get_dept_emps(10);

     

    PL/SQL procedure successfully completed.

     

    SQL> exec process_ref_cur(:rc);
    Column: 1 : NUMBER : EMPNO
    Column: 2 : VARCHAR2(10) : ENAME
    Column: 3 : NUMBER : MGR
    Column: 4 : NUMBER : SAL
    Column: 5 : DATE : HIREDATE
    7782
    CLARK
    7839
    2450
    09/06/1981 00:00:00
    7839
    KING
    5000
    17/11/1981 00:00:00
    7934
    MILLER
    7782
    1300
    23/01/1982 00:00:00

     

    PL/SQL procedure successfully completed.


    Well that works.  We now have details of our columns and we're actually fetching the data and basically formatting it, where necessary, depending on it's datatype, for display purposes.

     

    Ok, it's not very pretty output at the minute, but now we know we can get the column information and we're able to fetch the data, let's finally make some changes to make it look good...

    (We'll have to try and size the 'columns' of our output based on the datatype and datatype size, but we should be able to do that with a little substr and padding)

     

    create or replace procedure process_ref_cur(p_rc in out sys_refcursor) as
      dbms_cur    integer;
      rec_tab     dbms_sql.desc_tab2;
      col_cnt     integer;
      -- local variables for fetching data into
      fvarchar    varchar2(4000);
      fnumber     number;
      fdate       date;
      --
      ret         number;
      header      varchar2(4000) := '';
      rowcount    number := 0;
    begin
      -- Convert the incoming Ref Cursor to a DBMS_SQL cursor
      dbms_cur := dbms_sql.to_cursor_number(p_rc);

      -- Get the description of the cursor projection.
      -- This tells us the number of columns and a structured
      -- datatype containing information about each column.

      dbms_sql.describe_columns2(dbms_cur, col_cnt, rec_tab);


      -- 'print' the description as heading titles
      -- and at the same time define each column ready for fetching
      -- combining the two sections into one

      dbms_output.new_line; -- start with a blank line
      for j in 1..col_cnt
      loop
        case rec_tab(j).col_type
          when 1 then
            dbms_sql.define_column(dbms_cur, j, fvarchar, rec_tab(j).col_max_len);
            dbms_output.put(rpad(substr(rec_tab(j).col_name, 1, rec_tab(j).col_max_len), rec_tab(j).col_max_len, ' '));
            header := header||lpad('-', rec_tab(j).col_max_len, '-');
          when 2 then -- assume numbers will all fit in 10 characters and right align (left pad) names
            dbms_sql.define_column(dbms_cur, j, fnumber);
            dbms_output.put(lpad(substr(rec_tab(j).col_name, 1, 10), 10, ' '));
            header := header||lpad('-', 10, '-');
          when 12 then -- assume date format of DD/MM/YYYY HH24:MI:SS so need 19 characters
            dbms_sql.define_column(dbms_cur, j, fdate);
            dbms_output.put(rpad(substr(rec_tab(j).col_name, 1, 19), 19, ' '));
            header := header||lpad('-', 19, '-');
          when 96 then
            dbms_sql.define_column(dbms_cur, j, fvarchar, rec_tab(j).col_max_len); -- we'll treat char as varchar2
            header := header||lpad('-', rec_tab(j).col_max_len, '-');
        else -- assume any other datatype we will be able to implicitly convert to varchar2 and display as 30 characters
          dbms_sql.define_column(dbms_cur, j, fvarchar, 4000);
          dbms_output.put(rpad(substr(rec_tab(j).col_name, 1, 30), 30, ' '));
          header := header||lpad('-', 30, '-');
        end case;
        if j < col_cnt then
          dbms_output.put(' '); -- put a space between each column
          header := header||' ';
        end if;
      end loop;
      dbms_output.new_line;
      dbms_output.put_line(header); -- output the header bar under heading titles

     

      -- Fetching the data
      -- Fetch each row in a loop, until no row is fetched
      loop
        -- Tell the cursor to fetch the next row, and return the number of rows fetched
        ret := DBMS_SQL.FETCH_ROWS(dbms_cur);
        -- if no row was fetched, exit our loop as we've reached the end
        exit WHEN ret = 0;
        rowcount := rowcount + 1;

        -- now print out the data for this row
        for j in 1..col_cnt
        loop
          case rec_tab(j).col_type
            when 1 then
              dbms_sql.column_value(dbms_cur, j, fvarchar);
              dbms_output.put(rpad(NVL(substr(fvarchar,1,rec_tab(j).col_max_len),' '),rec_tab(j).col_max_len,' '));
            when 2 then
              dbms_sql.column_value(dbms_cur, j, fnumber);
              -- we'll right justify the numbers for neatness and determine if they fit in our 10 character number limit
              dbms_output.put(case when length(to_char(fnumber)) > 10 then '##########' else lpad(NVL(to_char(fnumber),' '),10,' ') end);
            when 12 then
              dbms_sql.column_value(dbms_cur, j, fdate);
              dbms_output.put(to_char(fdate,'DD/MM/YYYY HH24:MI:SS'));
            when 96 then
              dbms_sql.column_value(dbms_cur, j, fvarchar);
              dbms_output.put(rpad(NVL(substr(fvarchar,1,rec_tab(j).col_max_len),' '),rec_tab(j).col_max_len,' '));
          else
            dbms_sql.column_value(dbms_cur, j, fvarchar);
            -- other datatypes we'll handle roughly by truncating them if they're more than 30 characters
            dbms_output.put(rpad(NVL(substr(fvarchar,1,30),' '),30,' '));
          end case;
          if j < col_cnt then
            dbms_output.put(' '); -- put a space between each column
          end if;
        end loop;
        dbms_output.new_line;
      end loop;
      dbms_output.new_line;
      dbms_output.put_line(to_char(rowcount)||' rows selected.');
      dbms_output.new_line;

      -- finally, ensure we close the cursor to release resources
      dbms_sql.close_cursor(dbms_cur);
    end;
    /

     

    And our final result...

     

    SQL> set serverout on format wrapped
    SQL> set feedback off
    SQL> var rc refcursor
    SQL> exec :rc := get_dept_emps(10);
    SQL> exec process_ref_cur(:rc);

     

         EMPNO ENAME             MGR        SAL HIREDATE
    ---------- ---------- ---------- ---------- -------------------
          7782 CLARK            7839       2450 09/06/1981 00:00:00
          7839 KING                        5000 17/11/1981 00:00:00
          7934 MILLER           7782       1300 23/01/1982 00:00:00

     

    3 rows selected.

     

    SQL>


    Now it looks more like we've just issued a select statement in SQL*Plus, or used the print command on a ref cursor, yet our procedure has know prior knowledge of what the ref cursor looks like.


    So, why doesn't PL/SQL let us do this natively without having to do all this code using the DBMS_SQL package?
    In a sense it already does, but just like the 'print' command in SQL*Plus, when we write a static SQL statement in PL/SQL, or use a cursor loop (or cursor for loop), then PL/SQL does all the opening, defining, binding and fetching for us, making it appear that we can reference the data by column name rather than by position.  That's the power of the PL language, to seamlessly integrate SQL within it and abstract all the hard work away from us.  That's pretty awesome!


    This doesn't just apply to PL/SQL, it applies to any query you do.  When you write a SELECT statement directly in SQL*Plus, the SQL*Plus interface does all of the cursor processing for us, opening up a cursor, defining the columns, fetching the rows and formatting the results on the display before closing the cursor.  Other tools like SQL*Developer, or TOAD, or PL/SQL Developer etc. all do the same thing.  Even when you issue an SQL statement in PL/SQL directly, or as part of an explicit cursor loop etc. most of the hard work is just abstracted away and done for us.

     

    So, the next time you're thinking, "I'll use a ref cursor", just ask yourself "Do I really need to do that?".

     

    5. Summary

     

    In summary, the key things to remember are...

     

    REF CURSORS ARE NOT DATA CONTAINERS.  ONCE OPENED, THEY ARE SIMPLY A POINTER TO A QUERY THAT HAS YET TO FETCH DATA.

     

    THEY ARE TYPICALLY CONSUMED BY 3rd PARTY INTERFACES THAT ONLY HANDLE REF CURSORS AND ARE NOT REALLY INTENDED FOR USE WITHIN PL/SQL.  WHILST YOU CAN USE THEM IN PL/SQL, ALWAYS FIRST ASK YOURSELF IF YOU "SHOULD".


    References:
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#BABEDAHF
    http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci03typ.htm#LNOCI16268
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci05bnd.htm#i421494