In some databases, a stored procedure simply returns result sets to its caller. In PL/SQL the usual way is an OUT parameter of type SYS_REFCURSOR, which the caller must declare and fetch. DBMS_SQL.RETURN_RESULT offers the other style: a block or procedure hands open cursors to the client, which displays or reads them as implicit results after the call.
Code for This Guide
The main examples are in the examples/cursors 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
open c for select ...; dbms_sql.return_result(c);
Each call returns one result set; a block can return several. Clients such as SQLcl, SQL*Plus, JDBC, and python-oracledb read them after the block completes.
Return Two Result Sets from a Block
Example:
declare c1 sys_refcursor; c2 sys_refcursor; begin open c1 for select count(*) as flights from flights; dbms_sql.return_result(c1); open c2 for select status, count(*) as n from flights group by status order by status; dbms_sql.return_result(c2); end; /
Output:
PL/SQL procedure successfully completed.
ResultSet #1
FLIGHTS
----------
2856
ResultSet #2
STATUS N
---------- ----------
ARRIVED 2268
CANCELLED 50
SCHEDULED 538SQLcl shows each cursor as a numbered result set after the block finishes.
From a Procedure
A stored procedure can return results the same way, so the caller needs no cursor variable.
Example:
create or replace procedure route_report (p_origin char) is
c sys_refcursor;
begin
open c for select destination, distance_km from routes
where origin = p_origin order by distance_km;
dbms_sql.return_result(c);
end;
/
exec route_report('SIN')Output:
Procedure ROUTE_REPORT compiled PL/SQL procedure successfully completed. ResultSet #1 DES DISTANCE_KM --- ----------- NRT 5358 DXB 5845 SYD 6294
EXEC route_report('SIN') prints the routes from Singapore. The example drops the procedure afterward.
Implicit Results Compared with REF CURSOR Parameters
| DBMS_SQL.RETURN_RESULT | OUT SYS_REFCURSOR | |
|---|---|---|
| Caller declares a variable | No | Yes |
| Usable from PL/SQL callers | Through DBMS_SQL.GET_NEXT_RESULT | Directly |
| Typical use | Client applications, code migrated from other databases | PL/SQL APIs |
Related Guides
Conclusion
DBMS_SQL.RETURN_RESULT returns open cursors from a PL/SQL block or procedure as implicit result sets, which clients read after the call. Use it for client-facing procedures and migrated code, and OUT SYS_REFCURSOR parameters for APIs called from PL/SQL.
