Display REFCURSOR results through SQL*Plus
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).
- Kevin Meade's blog
- Log in to post comments
Comments
Just use SQL*Plus variable
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
thanks, I missed that one
Kevin
You are really a funny guy.
You are really a funny guy. Excellent post!!