DBMS_SQL cursors and ref cursors each have strengths. DBMS_SQL can describe columns and bind an unknown number of variables; ref cursors are easy to fetch from and to return to client programs. DBMS_SQL.TO_REFCURSOR and TO_CURSOR_NUMBER convert an open cursor from one kind to the other, so each part of the job uses the right tool.
Code for This Guide
The main examples are in the examples/pkg-dbms-sql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
rc := dbms_sql.to_refcursor(cursor_number); -- after EXECUTE c := dbms_sql.to_cursor_number(ref_cursor); -- after OPEN
After the conversion, use only the new cursor: the original one can no longer be used.
Convert in Both Directions
Example:
declare
c integer := dbms_sql.open_cursor;
rc sys_refcursor;
v_n integer;
v_city varchar2(40);
begin
dbms_sql.parse(c, 'select city from airports where is_hub', dbms_sql.native);
v_n := dbms_sql.execute(c);
rc := dbms_sql.to_refcursor(c); -- hand the open cursor over as a ref cursor
fetch rc into v_city;
dbms_output.put_line('hub: ' || v_city);
close rc;
open rc for select count(*) from routes;
c := dbms_sql.to_cursor_number(rc); -- and back, to describe or fetch with DBMS_SQL
dbms_output.put_line('open? ' || case when dbms_sql.is_open(c) then 'yes' else 'no' end);
dbms_sql.close_cursor(c);
end;
/Output:
hub: Dubai open? yes PL/SQL procedure successfully completed.
The query parsed and executed with DBMS_SQL becomes a ref cursor, which a normal FETCH reads. Then a ref cursor opened with OPEN FOR becomes a DBMS_SQL cursor number, open and ready for DBMS_SQL calls.
Describe a Ref Cursor
A function returns a ref cursor whose columns the caller does not know. Converting it lets DBMS_SQL describe it.
Example:
create or replace function fleet_report return sys_refcursor is
rc sys_refcursor;
begin
open rc for select type_code, count(*) as aircraft from aircraft group by type_code;
return rc;
end;
/
declare
rc sys_refcursor := fleet_report; -- a ref cursor of unknown shape
c integer;
v_cols dbms_sql.desc_tab3;
v_n integer;
begin
c := dbms_sql.to_cursor_number(rc);
dbms_sql.describe_columns3(c, v_n, v_cols);
for i in 1 .. v_n loop
dbms_output.put_line(i || ': ' || v_cols(i).col_name);
end loop;
dbms_sql.close_cursor(c);
end;
/Output:
Function FLEET_REPORT compiled 1: TYPE_CODE 2: AIRCRAFT PL/SQL procedure successfully completed.
The caller learns that the cursor has TYPE_CODE and AIRCRAFT columns, and could now define and fetch them. The example drops the function afterward.
Things to Know
- TO_REFCURSOR needs a cursor that was parsed and executed, and only for a query.
- TO_CURSOR_NUMBER needs an open ref cursor; if rows were already fetched, DBMS_SQL continues from the next row.
- A typical use is to build a query with any number of binds in DBMS_SQL, then return it as a SYS_REFCURSOR to a client.
Related Guides
- How to Return ResultSet from Stored Procedure in Oracle?
- How to Describe a Query at Run Time with DBMS_SQL.DESCRIBE_COLUMNS
Conclusion
DBMS_SQL.TO_REFCURSOR and TO_CURSOR_NUMBER switch an open cursor between DBMS_SQL and ref cursor form. Build and bind with DBMS_SQL and return a ref cursor, or describe and fetch an unknown ref cursor with DBMS_SQL.
