Display REFCURSOR results through SQL*Plus

Kevin Meade's picture
articles: 

Recently a friend asked me a simple question; "How do I display the results of a REFCURSOR through SQL*Plus?". The answer is of course is just as simple as the question, you "SELECT" it like anything else. He just had not seen the syntax before. Being me though, I wouldn't let him get away with such an easy offing, so I sent him instead a quick write-up on REFCURSORS and, the just as interesting, CURSOR EXPRESSIONS. This write-up shows various coding samples of how to use these to great effect. He liked it. He said it should be written up somewhere, and I knew just where. Hope you like it too. If you do, please add something in a reply, especially if you have a better or unique or interesting way of exploiting these features. I'd like to read about it, maybe even steal your code...

So Jarek, you want to know about REFCURSORS and how to see one in SQL*Plus. Well, here you go buddy, some examples of code you may find interesting. Lets start simple. Here is a select statement.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID 
------------------------------ ------- ---------- 
A                              TABLE      
DEPT                           TABLE      
EMP                            TABLE      
PLAN_TABLE                     SYNONYM    

4 rows selected.

Here is the same select statement but stuffed into what is called a CURSOR EXPRESSION. We use basically a type conversion to take a select statement and turn it into a (what would you call it uh...) a virtual cursor so to speak. Oracle calls these CURSOR EXPRESSIONS. Notice it has been named.

SQL> select cursor(select * from tab) jarek_refcursor
  2  from dual
  3  /

JAREK_REFCURSOR                           
--------------------                      
CURSOR STATEMENT : 1                      

CURSOR STATEMENT : 1

TNAME                          TABTYPE  CLUSTERID 
------------------------------ ------- ---------- 
A                              TABLE      
DEPT                           TABLE      
EMP                            TABLE      
PLAN_TABLE                     SYNONYM    

4 rows selected.

Looks kind of ugly, but don’t looks fool you, you can use this. So what exactly is it? Well let’s look at another piece of code that might help us understand. Here is a simple function that takes the same SQL and sends back a REFCURSOR. You are familiar with these I know (note the use of the newer SYS_REFCURSOR rather than a reference to a cursor type in some package somewhere, much cleaner).

SQL> create or replace
  2  function jarek_refcursor return sys_refcursor
  3  is
  4     c1 sys_refcursor;
  5  begin
  6     open c1 for select * from tab;
  7     return c1;
  8  end;
  9  /

Function created.

SQL> show errors
No errors.

OK, so here is the answer to your original question. To show a REFCURSOR in SQL*PLUS, just select it like you would select anything else, for in the end that is all it really is, a data item. It may have a special data type, but it is still just a data item.

SQL> select jarek_refcursor from dual
  2  /

JAREK_REFCURSOR                           
--------------------                      
CURSOR STATEMENT : 1                      

CURSOR STATEMENT : 1

TNAME                          TABTYPE  CLUSTERID 
------------------------------ ------- ---------- 
A                              TABLE      
DEPT                           TABLE      
EMP                            TABLE      
PLAN_TABLE                     SYNONYM    

4 rows selected.

What is more important is to recognize that the results of this function call are identical to the results of the CURSOR EXPRESSION shown above. So that aught to clue us as to the nature of what CURSOR EXPRESSIONS are; a form of REFCURSOR. I am sure there is a more precise description of CURSOR EXPRESSIONS and how they differ from REFCURSORS but in practical usage they behave the same.

That answers your original question. Knowing you as I do, I figure your response right about now is, "neat code but so what". Why would I want to use CURSOR EXPRESSIONS? They just look like a silly syntax for something I can already do. Well, they look silly here because the sample I have used is a bit silly. So lets check out something a little more real (not much more real but a little), and do stuff with some tables called DEPT, EMP, and TIMESHEET.

SQL> set linesize 60
SQL> desc dept
 Name                          Null?    Type
 ----------------------------- -------- ----------------
 DEPTNO                                 NUMBER
 DNAME                                  VARCHAR2(10)

SQL> desc emp
 Name                          Null?    Type
 ----------------------------- -------- ----------------
 EMPNO                                  NUMBER
 DEPTNO                                 NUMBER
 ENAME                                  VARCHAR2(10)

SQL> desc timesheet
 Name                          Null?    Type
 ----------------------------- -------- ----------------
 SOMEDATE                               DATE
 EMPNO                                  NUMBER
 HOURS                                  NUMBER

Yep, this is people, the company departments they work in, and their weekly timesheets. Stick with me; we are getting to some cool stuff soon.

SQL> select dept.*
  2  from dept
  3  /

    DEPTNO DNAME                          
---------- ---------------------
        10 D10                            

1 row selected.

SQL> 
SQL> select *
  2  from emp
  3  /

     EMPNO     DEPTNO ENAME               
---------- ---------- ----------
       100         10 JAREK               
       101         10 KEVIN               

2 rows selected.

SQL> 
SQL> select *
  2  from timesheet
  3  /

TIMEDATE       EMPNO      HOURS           
--------- ---------- ----------           
31-AUG-06        100         40           
24-AUG-06        100         40           
17-AUG-06        100         40           
31-AUG-06        101         40           
24-AUG-06        101         40           
17-AUG-06        101         40           

6 rows selected.

Just simple data, you and me, working in department 10 for the last 3 weeks. Hey look at those hours (40), no overtime, must have been good weeks. So check this out. Lets select each department and all the employee data that goes with it but do it as one row.

SQL> select dept.*
  2        ,cursor(
  3                select emp.*
  4                from emp
  5                where emp.deptno = dept.deptno
  6               ) emprows
  7  from dept
  8  /

    DEPTNO DNAME                          EMPROWS              
---------- ------------------------------ -------------------- 
        10 D10                            CURSOR STATEMENT : 3 

CURSOR STATEMENT : 3

     EMPNO     DEPTNO ENAME 
---------- ---------- ------------------------------
       100         10 JAREK 
       101         10 KEVIN

I looks like we selected three rows, but we didn't, we only selected one. The outer select clearly shows that we are selecting department rows and since there is only one department row in our data we selected only one row. But there are two employee rows attached. We did that by using a CURSOR EXPRESSION so that we could nest the employee rows with their associated department row. You can observe the correlation via the "where emp.deptno = dept.deptno" in the CURSOR EXPRESISSION select statement. We can continue to nest expressions as deep as necessary. Here is another example that also pulls in the timesheets of the employees.

SQL> select dept.*
  2        ,cursor(
  3                select emp.*
  4                      ,cursor(
  5                              select timesheet.*
  6                              from timesheet
  7                              where timesheet.empno = emp.empno
  8                             ) timesheetrows
  9                from emp
 10                where emp.deptno = dept.deptno
 11               ) emprows
 12  from dept
 13  /

    DEPTNO DNAME                          EMPROWS                               
---------- ------------------------------ --------------------                  
        10 D10                            CURSOR STATEMENT : 3                  

CURSOR STATEMENT : 3

     EMPNO     DEPTNO ENAME                          TIMESHEETROWS     
---------- ---------- ------------------------------ -------------------- 
       100         10 JAREK                          CURSOR STATEMENT : 4 

CURSOR STATEMENT : 4

TIMEDATE       EMPNO      HOURS           
--------- ---------- ----------           
31-AUG-06        100         40           
24-AUG-06        100         40           
17-AUG-06        100         40           

       101         10 KEVIN                          CURSOR STATEMENT : 4 

CURSOR STATEMENT : 4

TIMEDATE       EMPNO      HOURS
--------- ---------- ----------
31-AUG-06        101         40
24-AUG-06        101         40
17-AUG-06        101         40

For each department (we have only one), we nested with it, its employees, and for each employee we nested the proper timesheet rows. You can see this by walking the data shown in the output. Department followed by an employee followed by timesheet rows, backup to the next employee, then more timesheet rows. SQL*Plus just walks the data returned by the CURSOR EXPRESSIONS and since these expressions are correlated to their parent rows all the data is walked in order (so to speak). It gets better too because we can bring back entire trees of data with many levels and branches all in one select statement.

OK, but why, I mean, you have to process the data somehow, so even if you get it back in one select and then walk it using some kind of corresponding nested PL/SQL code (or something), how is that different from using multiple selects do to the same thing?

Well, it is different because of what it is. With multiple selects you are selecting multiple datasets and you therefore have to treat them as multiple datasets when you use them somewhere. With a single select that nests sets of rows, you have only one ultimate dataset which you can then use somewhere else as a single dataset. The trick is to find a program that will let you use this single dataset. SQL*Plus was one such program.

But luck is with use for here is another such program. One I wager you are going to see a lot more of.

SQL> set long 9999
SQL> select dbms_xmlgen.getxmltype(
  2    'select dept.*'||' '||
  3    '      ,cursor('||' '||
  4    '              select emp.*'||' '||
  5    '                    ,cursor('||' '||
  6    '                            select timesheet.*'||' '||
  7    '                            from timesheet'||' '||
  8    '                            where timesheet.empno = emp.empno'||' '||
  9    '                           ) timesheetrows'||' '||
 10    '              from emp'||' '||
 11    '              where emp.deptno = dept.deptno'||' '||
 12    '             ) emprows'||' '||
 13    'from dept'
 14                               ) deptrow
 15  from dual
 16  /

DEPTROW                                   
------------------------------------------------
<ROWSET>                                  
  <ROW>                                   
    <DEPTNO>10</DEPTNO>                   
    <DNAME>D10</DNAME>                    
    <EMPROWS>                             
      <EMPROWS_ROW>                       
        <EMPNO>100</EMPNO>                
        <DEPTNO>10</DEPTNO>               
        <ENAME>JAREK</ENAME>              
        <TIMESHEETROWS>                   
          <TIMESHEETROWS_ROW>             
            <TIMEDATE>31-AUG-06</TIMEDATE>
            <EMPNO>100</EMPNO>            
            <HOURS>40</HOURS>             
          </TIMESHEETROWS_ROW>            
          <TIMESHEETROWS_ROW>             
            <TIMEDATE>24-AUG-06</TIMEDATE>
            <EMPNO>100</EMPNO>            
            <HOURS>40</HOURS>             
          </TIMESHEETROWS_ROW>            
          <TIMESHEETROWS_ROW>             
            <TIMEDATE>17-AUG-06</TIMEDATE>
            <EMPNO>100</EMPNO>            
            <HOURS>40</HOURS>             
          </TIMESHEETROWS_ROW>            
        </TIMESHEETROWS>                  
      </EMPROWS_ROW>                      
      <EMPROWS_ROW>                       
        <EMPNO>101</EMPNO>                
        <DEPTNO>10</DEPTNO>               
        <ENAME>KEVIN</ENAME>              
        <TIMESHEETROWS>                   
          <TIMESHEETROWS_ROW>             
            <TIMEDATE>31-AUG-06</TIMEDATE>
            <EMPNO>101</EMPNO>            
            <HOURS>40</HOURS>             
          </TIMESHEETROWS_ROW>            
          <TIMESHEETROWS_ROW>             
            <TIMEDATE>24-AUG-06</TIMEDATE>
            <EMPNO>101</EMPNO>            
            <HOURS>40</HOURS>             
          </TIMESHEETROWS_ROW>            
          <TIMESHEETROWS_ROW>             
            <TIMEDATE>17-AUG-06</TIMEDATE>
            <EMPNO>101</EMPNO>            
            <HOURS>40</HOURS>             
          </TIMESHEETROWS_ROW>            
        </TIMESHEETROWS>                  
      </EMPROWS_ROW>                      
    </EMPROWS>                            
  </ROW>                                  
</ROWSET>

Wow, what happened here? I took the select statement we have, turned it into a quoted text string, and then passed it to one of Oracle's many XML routines. Wouldn't you know it, the Oracle routine fed me back an XML stream of the data I asked for.

Now consider all the complicated code those six guys on the other side of your cubical wrote last month to get XML from the Oracle database for that MARINE system. What a waste. You can do everything they did with no effort at all. You just put together a select statement of arbitrary complexity (that means with what ever you want in it), and tell Oracle to get you some XML. You can build as complex a tree of data and you need. Every version of the MARINE XML hierarchies they have, you can build with very little. Talk about opportunity, if there was ever a project you could milk for an easy time and look good, an Oracle XML project is it.

Once you get the data, you can transform it into any format you want using XSLT. There you go, give those six guys a reason for existing; they can write your XSLT converters for you. That is a crappy job, so let some else do it. We are Oracle Gurus; we get to do the good stuff (as we should). If you get an XML project going sometime around March next year, call me. I wanted to get paid for doing nothing this summer so I could spend more time with family in the pool.

Well that its buddy, you still owe me a lunch. I'll be by next week to collect so don't go on vacation like the last time.



About the Author: Kevin Meade is a 22-year veteran of Relational Databases in general and Oracle in particular. He is employed under many hats: Architect, Designer, Modeler, and Programmer and so is currently a contractor using the title of “Oracle Specialist” in the Finance and Insurance Industries. Married with two girls, he likes to swim in his heated pool with his family, is addicted to Strategy computer games, keeps a Saltwater Reef in his home, and brews his own dark beers (yum).

Comments

We can also define SQL*PLus variables of type REFCURSOR...

SQL> create or replace function get_emps(dno in number) return sys_refcursor
  2  is
  3    return_value sys_refcursor;
  4  begin
  5    open return_value for
  6      select * from emp where deptno = dno;
  7    return return_value;
  8  end;
  9  /

Function created.

SQL> var rc refcursor
SQL> exec :rc := get_emps(30)

PL/SQL procedure successfully completed.

SQL> print rc
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


6 rows selected.

SQL>

Cheers, APC

Kevin Meade's picture

Kevin

You are really a funny guy. Excellent post!!