Implicit Statement Results: Returning Result Sets Without Ref Cursors

The Old Way: Ref Cursors Everywhere

For years, the standard approach to returning query results from a PL/SQL procedure to a client application was to use REF CURSORS. You would declare an OUT parameter of type SYS_REFCURSOR, open a cursor for your query, and assign it to the parameter. The client application would then fetch rows from the cursor. While effective, this pattern required the caller to know about and manage the cursor parameter, and returning multiple result sets required multiple OUT parameters.

Oracle 12c Changed the Game

Oracle 12c introduced implicit statement results through the DBMS_SQL.RETURN_RESULT procedure. This feature allows a PL/SQL procedure to return one or more result sets to the calling client without any OUT parameters at all. The client receives the results automatically, much like how SQL Server stored procedures return result sets.

CREATE OR REPLACE PROCEDURE get_department_report(
   p_department_id IN NUMBER
)
IS
   c_employees SYS_REFCURSOR;
   c_projects  SYS_REFCURSOR;
   c_budget    SYS_REFCURSOR;
BEGIN
   -- First result set: employees in the department
   OPEN c_employees FOR
      SELECT employee_id, first_name, last_name, salary
        FROM employees
       WHERE department_id = p_department_id
       ORDER BY last_name;
   DBMS_SQL.RETURN_RESULT(c_employees);

   -- Second result set: active projects
   OPEN c_projects FOR
      SELECT project_id, project_name, start_date, target_end_date
        FROM projects
       WHERE department_id = p_department_id
         AND status = 'ACTIVE';
   DBMS_SQL.RETURN_RESULT(c_projects);

   -- Third result set: budget summary
   OPEN c_budget FOR
      SELECT budget_year, allocated_amount, spent_amount,
             allocated_amount - spent_amount AS remaining
        FROM department_budgets
       WHERE department_id = p_department_id
         AND budget_year >= EXTRACT(YEAR FROM SYSDATE) - 1;
   DBMS_SQL.RETURN_RESULT(c_budget);
END get_department_report;
/

Calling From SQL*Plus

The beauty of this approach is evident in SQL*Plus. Simply execute the procedure and the result sets display automatically:

SQL> EXEC get_department_report(30);

EMPLOYEE_ID FIRST_NAME   LAST_NAME    SALARY
----------- ------------ ------------ -------
        114 Den          Raphaely       11000
        115 Alexander    Khoo            3100
...

PROJECT_ID PROJECT_NAME          START_DATE TARGET_END_DATE
---------- --------------------- ---------- ---------------
      1001 Vendor Consolidation  01-MAR-24  30-SEP-24
...

BUDGET_YEAR ALLOCATED_AMOUNT SPENT_AMOUNT REMAINING
----------- ---------------- ------------ ---------
       2024          5000000      3200000   1800000
       2025          5500000       800000   4700000

Client Application Support

Most modern Oracle client libraries support implicit results. In Java (JDBC), you use getMoreResults() to iterate through the returned result sets. In Python with cx_Oracle (now python oracledb), you use cursor.getimplicitresults(). In .NET with ODP.NET, the results are accessible through the standard DataReader pattern.

Migration Convenience

This feature was specifically designed to ease migration from SQL Server to Oracle. SQL Server stored procedures naturally return result sets without explicit cursor parameters, and many migrated applications relied on this behavior. Implicit statement results provide a compatible pattern on the Oracle side, reducing the effort required to port stored procedure based applications.

When to Use Implicit Results vs Ref Cursors

Use implicit results when you want a clean procedure interface with no OUT parameters, when you need to return a variable number of result sets based on runtime conditions, or when you are migrating from SQL Server. Stick with explicit REF CURSOR OUT parameters when you need the calling PL/SQL code to consume the cursor directly (implicit results are designed for client consumption), or when your coding standards require explicit parameter documentation.