This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Jul 24, 2012 4:44 AM by Paulie RSS

PL/SQL 101 : Understanding Ref Cursors

BluShadow Guru Moderator
Currently Being Moderated
PL/SQL 101: Understanding Ref Cursors
Often on the forum we get a question 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"
And, equally as often, the problem comes from a lack of understanding what a ref cursor actually is.  So I thought I'd give a quick summary of the basics behind ref cursors with a view to giving a clearer understanding of them. (Please excuse any errors or ommissions)

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 passes it back..
(We'll just select some employee details for a specified department)
SQL> create or replace function get_dept_emps(p_deptno in number) return sys_refcursor is
  2    v_rc sys_refcursor;
  3  begin
  4    open v_rc for 'select empno, ename, mgr, sal from emp where deptno = :deptno' using p_deptno;
  5    return v_rc;
  6  end;
  7  /
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, and then call the function to get the ref cursor back..
(Note: SQL*Plus variable type of "refcursor" is the equivalent of PL/SQL's "sys_refcursor")
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 SQL*Plus' print command now 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 returns the rows we wanted from the employee table.

Let's look at that 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. In truth, 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 relevant 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! 
Therefore, 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 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 so that we can see the type of thing SQL*Plus us doing internally..
Firstly, let's get the open cursor and ask it how many rows it has..
SQL> ed
Wrote file afiedt.buf
  1  declare
  2    v_rc    sys_refcursor;
  3  begin
  4    v_rc := get_dept_emps(10);  -- This returns an open cursor
  5    dbms_output.put_line('Rows: '||v_rc%ROWCOUNT);
  6    close v_rc;
  7* end;
SQL> /
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 the 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..
SQL> ed
Wrote file afiedt.buf
  1  declare
  2    v_rc    sys_refcursor;
  3    v_empno number;
  4    v_ename varchar2(10);
  5    v_mgr   number;
  6    v_sal   number;
  7  begin
  8    v_rc := get_dept_emps(10);  -- This returns an open cursor
  9    dbms_output.put_line('Pre Fetch: Rows: '||v_rc%ROWCOUNT);
10    fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
11    dbms_output.put_line('Post Fetch: Rows: '||v_rc%ROWCOUNT);
12    close v_rc;
13* end;
SQL> /
Pre Fetch: Rows: 0
Post Fetch: Rows: 1
PL/SQL procedure successfully completed.
So let's fetch all our data and display it..
SQL> ed
Wrote file afiedt.buf
  1  declare
  2    v_rc    sys_refcursor;
  3    v_empno number;
  4    v_ename varchar2(10);
  5    v_mgr   number;
  6    v_sal   number;
  7  begin
  8    v_rc := get_dept_emps(10);  -- This returns an open cursor
  9    loop
10      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
11      exit when v_rc%NOTFOUND;  -- Exit the loop when we've run out of data
12      dbms_output.put_line('Row: '||v_rc%ROWCOUNT||' # '||v_empno||','||v_ename||','||v_mgr||','||v_sal);
13    end loop;
14    close v_rc;
15* end;
SQL> /
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..
SQL> ed
Wrote file afiedt.buf
  1  declare
  2    v_rc    sys_refcursor;
  3    v_empno number;
  4    v_ename varchar2(10);
  5    v_mgr   number;
  6    v_sal   number;
  7  begin
  8    v_rc := get_dept_emps(10);  -- This returns an open cursor
  9    loop
10      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
11      exit when v_rc%NOTFOUND;  -- Exit the loop when we've run out of data
12      dbms_output.put_line('Row: '||v_rc%ROWCOUNT||' # '||v_empno||','||v_ename||','||v_mgr||','||v_sal);
13    end loop;
14    close v_rc;
15    fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
16* end;
SQL> /
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. 
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 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 so the type must be a database object..
SQL> create or replace type emptype as object(empno number,
  2                                           ename varchar2(10),
  3                                           mgr   number,
  4                                           sal   number);
  5  /
Type created.
SQL> create or replace type t_emptype as table of emptype;
  2  /
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..
SQL> ed
Wrote file afiedt.buf
  1  create or replace function populate_emps(deptno in number := null)
  2  return t_emptype is
  3    v_emptype t_emptype := t_emptype();  -- Declare a local table structure and initialize it
  4    v_cnt     number := 0;
  5    v_rc      sys_refcursor;
  6    v_empno   number;
  7    v_ename   varchar2(10);
  8    v_mgr     number;
  9    v_sal     number;
10  begin
11    v_rc := get_dept_emps(deptno);
12    loop
13      fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
14      exit when v_rc%NOTFOUND;
15      v_emptype.extend;
16      v_cnt := v_cnt + 1;
17      v_emptype(v_cnt) := emptype(v_empno, v_ename, v_mgr, v_sal);
18    end loop;
19    close v_rc;
20    return v_emptype;
21* end;
SQL> /
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 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..
SQL> 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.
and
SQL> 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.
SQL>
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.
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 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.
There is a way around this however.  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 (http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sql.htm#CHDJDGDG).
DBMS_SQL is a very powerful way of writing dynamic SQL, but that's for another article. 

Summary
In summary, the one key thing to remember is..
REF CURSORS ARE NOT DATA CONTAINERS.  ONCE OPENED, THEY ARE SIMPLY A POINTER TO A QUERY THAT HAS YET TO FETCH DATA.
Other articles in the PL/SQL 101 series:
  • 1. Re: PL/SQL 101 : Understanding Ref Cursors
    KeithJamieson Expert
    Currently Being Moderated
    excellent article. However there is one thing which is slightly erroneous. You don't need a type to be declared in the database to fetch the data, but you do need to declare a type;

    here is one of my unit test scripts that does just that.

    DECLARE
    PN_CARDAPPL_ID NUMBER;
    v_Return Cci_Standard.ref_cursor;
    type getcardapplattrval_recordtype
    Is record
    (cardappl_id ci_cardapplattrvalue.cardappl_ID%TYPE,
    tag ci_cardapplattrvalue.tag%TYPE,
    value ci_cardapplattrvalue.value%TYPE
    );
    getcardapplattrvalue_record getcardapplattrval_recordtype;
    BEGIN
    PN_CARDAPPL_ID := 1; --value must be supplied
    v_Return := CCI_GETCUSTCARD.GETCARDAPPLATTRVALUE(
    PN_CARDAPPL_ID => PN_CARDAPPL_ID
    );
    loop
    fetch v_return
    into getcardapplattrvalue_record;

    dbms_output.put_line('Cardappl_id=>'||getcardapplattrvalue_record.cardappl_id);
    dbms_output.put_line('Tag =>'||getcardapplattrvalue_record.tag);
    dbms_output.put_line('Value =>'||getcardapplattrvalue_record.value);
    exit when v_Return%NOTFOUND;
    end loop;
    END;
  • 2. Re: PL/SQL 101 : Understanding Ref Cursors
    BluShadow Guru Moderator
    Currently Being Moderated
    Keith Jamieson wrote:
    excellent article. However there is one thing which is slightly erroneous. You don't need a type to be declared in the database to fetch the data, but you do need to declare a type;
    Correct, if you are just fetching the data in PL/SQL. The article, however, is talking about fetching the data within SQL. You won't do that with a locally defined PL/SQL type. ;)
  • 3. Re: PL/SQL 101 : Understanding Ref Cursors
    692864 Newbie
    Currently Being Moderated
    thank you so much for a great article its very nice and helpful

    thanks and regards
    raj
  • 4. Re: PL/SQL 101 : Understanding Ref Cursors
    563884 Newbie
    Currently Being Moderated
    Thanks, The article was quite helpful in understanding the concept.

    I am developing a web site in ASP.Net and using Oracle 10g data base in backend. My idea is to base each and every LOV and DataGrid on some Oracle PL/SQL procedure. As the procedure will have to returns records. So I will a ref cursor in out parameter. What do you think I am doing the right thing???
  • 5. Re: PL/SQL 101 : Understanding Ref Cursors
    707333 Newbie
    Currently Being Moderated
    Another developer is trying to call a PL/SQL procedure I created that returns a REF CURSOR from another PL/SQL procedure. It was written to fetch data for a Web Application. He then tries to fetch the returned REF CURSOR into a "local" REF CURSOR. I told him that I had never seen this done and believed it was not valid. Am I wrong?
  • 6. Re: PL/SQL 101 : Understanding Ref Cursors
    6363 Guru
    Currently Being Moderated
    This is a how to article and not the place to post new questions.
    user7734272 wrote:

    He then tries to fetch the returned REF CURSOR into a "local" REF CURSOR. I told him that I had never seen this done and believed it was not valid.
    And this how to article, if you read it also contains the answer to this question
    BluShadow wrote:

    A common mistake is that people believe that a ref cursor actually contains the result data from the query.
    In truth, the ref cursor doesn't contain any data at all, it's just a pointer to the query.
    So no, ref cursors do not contain data so cannot be fetched into. They can be assigned to though what the point of that would be I don't know and they are also primarily used for passing data to a non-PL/SQL environment like your original use of it, not for further processing in PL/SQL.
    SQL> var c1 refcursor
    SQL> var c2 refcursor
    SQL> begin
      2    open :c1 for select * from dual;
      3    :c2 := :c1;
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SQL> print c2
    
    D
    -
    X
    
    SQL>
  • 7. Re: PL/SQL 101 : Understanding Ref Cursors
    247514 Expert
    Currently Being Moderated
    Nice work Blue and bump. The forum should have sticky or FAQ kinda list for each forum.
  • 8. Re: PL/SQL 101 : Understanding Ref Cursors
    764763 Newbie
    Currently Being Moderated
    Hi everyone !!

    I have a question to ask you, and i hope you can help me.

    Are Ref Cursors the best solution to call stored procedure from java code ?
    Or there's another "best" way to do it ?

    And thnx
  • 9. Re: PL/SQL 101 : Understanding Ref Cursors
    BluShadow Guru Moderator
    Currently Being Moderated
    user11951318 wrote:
    Hi everyone !!

    I have a question to ask you, and i hope you can help me.

    Are Ref Cursors the best solution to call stored procedure from java code ?
    Or there's another "best" way to do it ?

    And thnx
    If you mean, "Are Ref Cursors the best thing to pass back from a stored procedure in order to query data back from the database?" then I would guess the answer is Yes as that is what they are primarily intended for. You can't "call" a stored procedure using a ref cursor as your question implies. However, this is an Oracle forum, not a Java forum, so the best practice for Java would be best asked on a Java forum.
  • 10. Re: PL/SQL 101 : Understanding Ref Cursors
    743277 Newbie
    Currently Being Moderated
    I am executing this code toad but it does show the output of query , How can i get the out put of this code , I M USING TOAD

    set serveroutput on ;
    DECLARE
    CURSOR c8
    IS
    SELECT (a.invoice_id) l_name,
    (a.invoice_id) f_name
    FROM ap_invoices_all a;
    BEGIN
    FOR r_c8 IN c8
    LOOP
    DBMS_OUTPUT.put_line (r_c8.l_name || ' ' || r_c8.l_name);
    END LOOP;
    END;
  • 11. Re: PL/SQL 101 : Understanding Ref Cursors
    Boneist Guru
    Currently Being Moderated
    Either add the following line before your procedure and run it as a script:
    set serveroutput on
    Or click the red button (it will turn green) on the DBMS Output (Disabled) tab, run your query and check in that tab for the dbms_output output.
  • 12. Re: PL/SQL 101 : Understanding Ref Cursors
    BluShadow Guru Moderator
    Currently Being Moderated
    user11995078 wrote:
    I am executing this code toad but it does show the output of query , How can i get the out put of this code , I M USING TOAD
    Boneist has already given you the answer, but in future, consider posting your question on a thread of your own rather than hijacking something that is completely unrelated to your question.
  • 13. Re: PL/SQL 101 : Understanding Ref Cursors
    743277 Newbie
    Currently Being Moderated
    thanks Boss
  • 14. Re: PL/SQL 101 : Understanding Ref Cursors
    user10857924 Journeyer
    Currently Being Moderated
    Hi blueshadow,

    Nice explanation :)

    Just quick question in the below code you are closing the ref cursor before fetching is that correct?

    SQL> ed
    Wrote file afiedt.buf

    1 declare
    2 v_rc sys_refcursor;
    3 v_empno number;
    4 v_ename varchar2(10);
    5 v_mgr number;
    6 v_sal number;
    7 begin
    8 v_rc := get_dept_emps(10); -- This returns an open cursor
    9 loop
    10 fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
    11 exit when v_rc%NOTFOUND; -- Exit the loop when we've run out of data
    12 dbms_output.put_line('Row: '||v_rc%ROWCOUNT||' # '||v_empno||','||v_ename||','||v_mgr||','||v_sal);
    13 end loop;
    14 close v_rc;
    15 fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
    16* end;
    SQL> /
    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
1 2 Previous Next