Difference Between Cursor And Ref Cursor


In this post I am trying to detail out the differences between Cursor and Ref Cursors. I am sure, this question has been asked in many interviews to many of us and it also has its own technical weightage. Lets first check the basic example of Cursor and Ref Cursors. In this post Cursor means PL/SQL Cursors only.

Example of Cursor:
declare    
    cursor c1 is select ename, sal from scott.emp;
begin    
    for c in c1 
    loop
        dbms_output.put_line('Ename: ' || c.ename || ', Salary: ' || c.sal);
    end loop;
end;
/

Example of Ref Cursor
declare    
    c1 SYS_REFCURSOR;
    ename varchar2(10);
    sal number;
begin
    open c1 for select ename, sal from scott.emp;
    LOOP 
        FETCH c1 into ename, sal;
            EXIT WHEN c1%NOTFOUND;
        dbms_output.put_line('Ename: ' || ename || ', Salary: ' || sal);
    END LOOP;
    close c1;    
end;
/

Technically, They are both cursors and can be processed in the same fashion and at the most basic level, they both are same. There are some important differences between regular cursors and ref cursors which are following:

1) A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.

2) A ref cursor is defined at runtime and can be opened dynamically but a regular cursor is static and defined at compile time.

3) A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.

4) A ref cursor incurs a parsing penalty because it cannot cached but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.

5) A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.

6) A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.

My recommendation on ref cursors:
Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients and when there is NO other efficient/effective means of achieving the goal.


Related Links
- Cursor with Parameters in Oracle
- Ref Cursor: Strongly Typed VS Weakly Typed
- SQL Interview Question Answers
- View results of refcursor out parameter
- ORA-01422: exact fetch returns more than requested number of rows
Get Results from REFCURSOR with unknown number of columns in PLSQL

18 comments:

  1. You did not close c1 in your 2nd example.

    ReplyDelete
  2. Good post. thanks!

    ReplyDelete
  3. Would a good example of ref cursor be one where it is leveraged as a pointer to the underlying dataset?
    Mostly in transactional systems both in client side and server side processing.

    ReplyDelete
  4. example of ref cursor usage as a pointer to a dataset being passed around .. would be nice

    ReplyDelete
  5. Which editor you are using for the blog post creation?

    ReplyDelete
  6. chandra ekajaya
    I'm truly enjoying the design and layout of your website.

    It's a very easy on the eyes which makes it much more enjoyable for me to come here and visit
    more often. Did you hire out a designer to create your theme?
    Excellent work!

    ReplyDelete
  7. I have query about when we have to use nested,array and pl sql table give ans with real scenario example.

    ReplyDelete
  8. why can't we use normal cursor for passing data to another PL/SQL routine

    ReplyDelete
  9. Hi Nimish, Good to know these differences and i have a doubt in the point 5( A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be) , i hope we can do like this
    CREATE OR REPLACE PACKAGE demo AS

    TYPE rcursor IS REF CURSOR;

    PROCEDURE GetEmployeesInDept(c OUT ref_cursor);

    END demo;

    Please correct me if i am wrong

    Thanks in advance

    ReplyDelete
    Replies
    1. I was referring to cursor variable, check following code
      SQL> CREATE OR REPLACE PACKAGE demo AS
      2
      3 c1 SYS_REFCURSOR;
      4
      5 END demo;
      6 /

      Warning: Package created with compilation errors.

      SQL> show error
      Errors for PACKAGE DEMO:

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      3/4 PL/SQL: Declaration ignored
      3/4 PLS-00994: Cursor Variables cannot be declared as part of a
      package

      Delete
  10. I like the valuable information you provide in your articles.
    I will bookmark your weblog and check again here frequently.
    I am quite sure I'll learn a lot of new stuff right
    here! Best of luck for the next!

    ReplyDelete