It’s new option to return many ref cursors in PL/SQL implicit.

When code is migrated to Oracle Database from other vendors’ environments (Microsoft etc.), the capability will remove the need to rewrite code that takes advantage of implicit result set communication.

This feature is delivered by DBMS_SQL.RETURN_RESULT procedure which is responsible for creation of implicit result sets. Following procedure hasn’t got any explicit declaration of out variables. I love it :).

create or replace procedure return_many_cursors
authid current_user
is
  v_c1 sys_refcursor;
  v_c2 sys_refcursor;
  v_c3 sys_refcursor;
begin
  open v_c1 for 'select object_name from user_objects';

  dbms_sql.return_result(v_c1);

  open v_c2 for 'select instance_name from v$instance';

  dbms_sql.return_result(v_c2);

  open v_c3 for 'select name from v$database';

  dbms_sql.return_result(v_c3);
end;
/

sqlplus automatically display all result sets. Fantastic now I can easily create reports using PL/SQL to display them in sqlplus. No hard work just fun.

SQL> exec return_many_cursors;
PL/SQL procedure successfully completed.

ResultSet #1
OBJECT_NAME
----------------------
RETURN_MANY_CURSORS

ResultSet #2
INSTANCE_NAME
----------------
ora12c

ResultSet #3
NAME
---------
ORA12C

There is additional procedure DBMS_SQL.GET_NEXT_RESULT which allows to process manually generated result sets in PL/SQL.

set serveroutput on
declare
  v_cur     pls_integer;
  v_refcur  sys_refcursor;
  v_ret     pls_integer;
  v_col     varchar2(30);
begin
  v_cur := dbms_sql.open_cursor
           (treat_as_client_for_results => true);

  dbms_sql.parse
  ( c => v_cur, 
    statement => 'BEGIN return_many_cursors; END;',
    language_flag => DBMS_SQL.native );

  v_ret := dbms_sql.execute(v_cur);

  loop
    begin
      dbms_sql.get_next_result(v_cur, v_refcur);
    exception
      when no_data_found then
        exit;
    end;

    dbms_output.put_line(chr(10)||'---------------------');
    dbms_output.put_line('-  get_next_result  -');
    dbms_output.put_line('---------------------');

    loop
      fetch v_refcur into v_col;
      exit when v_refcur%notfound;

      dbms_output.put_line(v_col);
    end loop;

     close v_refcur;
  end loop;
end;
/
anonymous block completed

---------------------
-  get_next_result  -
---------------------
RETURN_MANY_CURSORS

---------------------
-  get_next_result  -
---------------------
ora12c

---------------------
-  get_next_result  -
---------------------
ORA12C

Have a fun 🙂

Tomasz