Implicit Statement Results in Oracle Database 12c

Implicit statement results will simplify the migration of stored procedures from other database engines to Oracle 12c onward.

◉ Background

Spend more than a few minutes with a Transact-SQL developer and you will have a conversation something like this.

- T-SQL Developer: How do I pass results out of a stored procedure?
- Oracle Developer: You use an out parameter.
- T-SQL Developer: But I want to return a resultset.
- Oracle Developer: No problem, make the out parameter a ref cursor and you're laughing.
- T-SQL Developer: So I have to define out parameters for each of the resultsets I want to pass out?
- Oracle Developer: Yes.
- T-SQL Developer: Oh man! Oracle Sucks!

This type of conversation comes about because Transact-SQL allows implicit returns of results from queries. For example, the following Transact-SQL stored procedure implicitly returns the results of the query.

CREATE PROCEDURE Get_My_Results
( @p_id  int )
AS
SELECT description, created_date FROM t1 WHERE id = @p_id
RETURN 0
GO

Oracle 12c now supports similar functionality using the RETURN_RESULT procedure in the DBMS_SQL package.

I can't imagine using this feature in the context of a regular PL/SQL programming environment, but it will be extremely valuable in any migration projects.

◉ RETURN_RESULT

Rather than defining explicit ref cursor out parameters, the RETURN_RESULT procedure in the DBMS_SQL package allows you to pass them out implicitly. To see an example of this, create the following table.

CREATE table t1 (
  id           NUMBER,
  description  VARCHAR2(30),
  created_date DATE
);

INSERT INTO t1 VALUES (1, 'The value 1', SYSDATE-2);
INSERT INTO t1 VALUES (2, 'The value 2', SYSDATE-1);
INSERT INTO t1 VALUES (3, 'The value 3', SYSDATE);
COMMIT;

Now we can create a stored procedure to return one or more resultsets containing this data.

CREATE OR REPLACE PROCEDURE get_my_results (p_id IN NUMBER DEFAULT NULL)
AS
  l_cursor_1 SYS_REFCURSOR;
  l_cursor_2 SYS_REFCURSOR;
BEGIN
  IF p_id IS NOT NULL THEN
    OPEN l_cursor_1 FOR
      SELECT description, created_date
      FROM   t1
      WHERE  id = p_id;

    DBMS_SQL.RETURN_RESULT(l_cursor_1);
  END IF;

  OPEN l_cursor_2 FOR
    SELECT COUNT(*)
    FROM   t1;

  DBMS_SQL.RETURN_RESULT(l_cursor_2);
END;
/

If we execute this stored procedure from SQL*Plus, it will automatically display the implicit statement results.

SQL> EXEC get_my_results(1);

PL/SQL procedure successfully completed.

ResultSet #1

DESCRIPTION                    CREATED_DATE
------------------------------ --------------------
The value 1                    06-JUL-2013 21:19:45

1 row selected.

ResultSet #2

  COUNT(*)
----------
         3

1 row selected.

SQL> EXEC get_my_results;

PL/SQL procedure successfully completed.

ResultSet #1

  COUNT(*)
----------
         3

1 row selected.

SQL>

There is also an overload of the RETURN_RESULT procedure that returns a cursor using a DBMS_SQL cursor ID, but I can't see this being useful for a Java or C# developer, so I will not cover it here.

◉ GET_NEXT_RESULT

Typically we would expect these resultsets to be processed by a client programming language, like Java or C#, but they can be processed in PL/SQL using the GET_NEXT_RESULT procedure in the DBMS_SQL package.

The following code sample executes the procedure using the DBMS_SQL package. Since the procedure can return multiple resultsets with differing record structures (variant resultsets), we have to describe the resultset to see how to process it. The decision is made based on the number of columns in the resultset.

SET SERVEROUTPUT ON
DECLARE
  l_sql_cursor    PLS_INTEGER;
  l_ref_cursor    SYS_REFCURSOR;
  l_return        PLS_INTEGER;

  l_col_cnt       PLS_INTEGER;
  l_desc_tab      DBMS_SQL.desc_tab2;

  l_count         NUMBER;
  l_description   t1.description%TYPE;
  l_created_date  t1.created_date%TYPE;
BEGIN
  -- Execute the function.
  l_sql_cursor := DBMS_SQL.open_cursor(treat_as_client_for_results => TRUE);

  DBMS_SQL.parse(c             => l_sql_cursor,
                 statement     => 'BEGIN get_my_results(1); END;',
                 language_flag => DBMS_SQL.native);

  l_return := DBMS_SQL.execute(l_sql_cursor);

  -- Loop through retrieving every resultset.
  LOOP
    -- Get the next resultset.
    BEGIN
      DBMS_SQL.get_next_result(l_sql_cursor, l_ref_cursor);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    -- Describe the resultset, to check the number of columns.
    l_return := DBMS_SQL.to_cursor_number(l_ref_cursor);
    DBMS_SQL.describe_columns2 (l_return, l_col_cnt, l_desc_tab);
    l_ref_cursor := DBMS_SQL.to_refcursor(l_return);

    -- Process the result set according to the number of columns.
    CASE l_col_cnt
      WHEN 1 THEN
        DBMS_OUTPUT.put_line('It must be the COUNT');
        FETCH l_ref_cursor
        INTO  l_count;

        DBMS_OUTPUT.put_line('l_count=' || l_count);
        CLOSE l_ref_cursor;
      WHEN 2 THEN
        DBMS_OUTPUT.put_line('It must be the DESCRIPTION and CREATED_DATE.');
        LOOP
          FETCH l_ref_cursor
          INTO  l_description, l_created_date;

          EXIT WHEN l_ref_cursor%NOTFOUND;

          DBMS_OUTPUT.put_line('l_description=' || l_description || '  ' ||
                               'l_created_date=' || TO_CHAR(l_created_date, 'DD-MON-YYYY'));
        END LOOP;
        CLOSE l_ref_cursor;
      ELSE
        DBMS_OUTPUT.put_Line('I wasn''t expecting that!');
    END CASE;
  END LOOP;
END;
/
It must be the DESCRIPTION and CREATED_DATE.
l_description=The value 1  l_created_date=06-JUL-2013
It must be the COUNT
l_count=3

PL/SQL procedure successfully completed.

SQL>

There is also an overload of the GET_NEXT_RESULT procedure that returns a cursor using a DBMS_SQL cursor ID.