Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: REF Cursors
Oracle Tips by Burleson
 

A REF CURSOR is a reference to a cursor, kind of a pointer to a pointer.  If you are coming from a C background, you already know the usefulness of that.

A REF CURSOR can be used as a parameter to a procedure.  That means that you can open a cursor and send back the pointer to that cursor allowing you to stream results to other procedures (even procedures written in other languages).

There are two types of static ref cursor: a strongly typed ref cursor and a weakly typed ref cursor.  A strongly typed ref cursor has the column structure of the cursor defined at compile time.  That means that you have associated a record type with the ref cursor when you were writing your code.

The syntax of a strongly typed cursor is:

TYPE <cursor name> IS REF CURSOR RETURN <table%ROWTYPE|record type name>;

A weakly typed cursor has no predefined column structure.  It is declared as a ref cursor but the structure associated with it is defined by its use rather than its declaration. 

The syntax of a weakly typed cursor is:

TYPE <cursor name> IS REF CURSOR;

You have probably noticed that I created a type as I did for collections and record types.  Unlike a static cursor, to use this ref cursor, I would need to declare a variable of that cursor type.

* A shortcut to creating a ref cursor is to use SYS_REFCURSOR as the data type.

Other than the declaration, a ref cursor works like a normal cursor.  Cursor attributes are available and you use the OPEN, FETCH and CLOSE syntax.

Below is an example showing several uses of ref cursors.

DECLARE
  v_emp_rec emp%ROWTYPE; 

  TYPE r_depts IS RECORD (
    ename emp.ename%TYPE,
    dname dept.dname%TYPE ); 

  v_depts_type r_depts; 

  TYPE c_employees IS REF CURSOR RETURN emp%ROWTYPE;
  v_employees c_employees;
 

  TYPE c_depts IS REF CURSOR RETURN r_depts;
  v_depts c_depts; 

  TYPE c_more_emps IS REF CURSOR;
  v_more_emps c_more_emps; 

  v_yet_more_emps SYS_REFCURSOR;

BEGIN 

  --  Cursor 1

  OPEN v_employees FOR SELECT * FROM emp LIMIT 5;
 
LOOP
    FETCH v_employees INTO v_emp_rec;
    EXIT WHEN v_employees%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( 'v_employees Loop - Ename: ' || v_emp_rec.ename );

  END LOOP;

  CLOSE v_employees; 

  --  Cursor 2

  OPEN v_depts FOR SELECT emp.ename, dept.dname
                     FROM emp,
                          dept LIMIT 5;

  LOOP

  
  FETCH v_depts INTO v_depts_type;
    EXIT WHEN v_depts%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( 'v_depts_type Loop - ename: ' ||
                           v_depts_type.ename || ', dname: ' ||
                           v_depts_type.dname );

  END LOOP;

  CLOSE v_depts; 

  --  Cursor 3

  OPEN v_more_emps FOR SELECT * FROM emp LIMIT 5;
  LOOP

    FETCH v_more_emps INTO v_emp_rec;
    EXIT WHEN v_more_emps%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( 'v_more_emps Loop - Ename: ' ||
                          v_emp_rec.ename );

  END LOOP;

  CLOSE v_more_emps; 

  --  Cursor 4

  OPEN v_yet_more_emps FOR SELECT * FROM emp LIMIT 5;
 
LOOP
    FETCH v_yet_more_emps INTO v_emp_rec;
    EXIT WHEN v_yet_more_emps%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( 'v_yet_more_emps Loop - Ename: ' ||
                          v_emp_rec.ename );

  END LOOP;

  CLOSE v_yet_more_emps; 

END;

INFO:  v_employees Loop - Ename: SMITH
INFO:  v_employees Loop - Ename: ALLEN
INFO:  v_employees Loop - Ename: WARD
INFO:  v_employees Loop - Ename: JONES
INFO:  v_employees Loop - Ename: MARTIN
INFO:  v_depts_type Loop - ename: SMITH, dname: ACCOUNTING
INFO:  v_depts_type Loop - ename: ALLEN, dname: ACCOUNTING
INFO:  v_depts_type Loop - ename: WARD, dname: ACCOUNTING
INFO:  v_depts_type Loop - ename: JONES, dname: ACCOUNTING
INFO:  v_depts_type Loop - ename: MARTIN, dname: ACCOUNTING
INFO:  v_more_emps Loop - Ename: SMITH
INFO:  v_more_emps Loop - Ename: ALLEN
INFO:  v_more_emps Loop - Ename: WARD
INFO:  v_more_emps Loop - Ename: JONES
INFO:  v_more_emps Loop - Ename: MARTIN
INFO:  v_yet_more_emps Loop - Ename: SMITH
INFO:  v_yet_more_emps Loop - Ename: ALLEN
INFO:  v_yet_more_emps Loop - Ename: WARD
INFO:  v_yet_more_emps Loop - Ename: JONES
INFO:  v_yet_more_emps Loop - Ename: MARTIN

The things to notice about this code are the way the cursors are declared.

Cursor 1 is declared as a %ROWTYPE, strongly typed ref cursor and the select statement is tied directly to the emp table's column structure. This type of declaration is best when returning a complete row of data.  If the table structure changes, the ref cursor and query will not need to be modified.  The changes will be picked up automatically.

Cursor 2 is declared as a strongly typed cursor of a RECORD type.  This ref cursor is tied directly to the structure of the r_depts. record type.  If you change r_depts., the columns available in the cursor will change and the query will need to be changed to fit. 

Cursor 3 declares a weakly typed cursor.  This cursor is not tied to a column structure until it is called below.  If the query changes, the ref cursor is still usable.

The real power of REF Cursors is their ability to be dynamically typed and the ability to be parameterized in stored procedures.  The section immediately below will show you how to use dynamic SQL.

             

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter