12c Implicit Result Sets

Starting with Oracle 12c 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

rajesh@PDB1> declare

  2     c sys_refcursor;

  3     l_cursor number;

  4     l_sql varchar2(100);

  5     l_rows number;

  6  begin

  7     open c for

  8             select dname from dept;

  9     l_cursor := dbms_sql.open_cursor;

 10     l_sql :=' select ename from emp '||

 11                     ' where deptno = 10 ';

 12     dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);

 13     l_rows := dbms_sql.execute(l_cursor);

 14     dbms_sql.return_result(c);

 15     dbms_sql.return_result(l_cursor);

 16  end;

 17  /

PL/SQL procedure successfully completed.

ResultSet #1

DNAME

--------------

ACCOUNTING

RESEARCH

SALES

OPERATIONS

4 rows selected.

ResultSet #2

ENAME

----------

CLARK

KING

MILLER

3 rows selected.

rajesh@PDB1>

Typically we would expect these result sets to be processed by a client programming language like Java or C#, but they can now be processed in PL/SQL using GET_NEXT_RESULT procedure (newly added up in 12c) within dbms_sql package.

rajesh@PDB1> create or replace procedure p

  2  as

  3     c sys_refcursor;

  4     l_cursor number;

  5     l_sql varchar2(100);

  6     l_rows number;

  7  begin

  8     open c for

  9             select dname from dept;

 10     l_cursor := dbms_sql.open_cursor;

 11     l_sql :=' select ename,sal from emp '||

 12                     ' where deptno = 10 ';

 13     dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);

 14     l_rows := dbms_sql.execute(l_cursor);

 15     dbms_sql.return_result(c);

 16     dbms_sql.return_result(l_cursor);

 17  end;

 18  /

Procedure created.

rajesh@PDB1> declare

  2     l_cursor number;

  3     l_refcursor sys_refcursor;

  4     l_rows number;

  5     l_cursor2 number;

  6     l_col_cnt number;

  7     l_desc_tab dbms_sql.desc_tab;

  8     l_name varchar2(20);

  9     l_sal number;

 10  begin

 11     l_cursor := dbms_sql.open_cursor

 12             (treat_as_client_for_results => TRUE) ;

 13     dbms_sql.parse

 14     ( c=> l_cursor,

 15       statement=>'begin p; end;' ,

 16       language_flag=> dbms_sql.native );

 17     l_rows := dbms_sql.execute(l_cursor);

 18     loop

 19             begin

 20                     dbms_sql.get_next_result(l_cursor,l_refcursor);

 21                     exception

 22                             when no_data_found then

 23                                     exit;

 24             end;

 25             l_cursor2 := dbms_sql.to_cursor_number(l_refcursor);

 26             dbms_sql.describe_columns(l_cursor2,l_col_cnt,l_desc_tab);

 27             l_refcursor := dbms_sql.to_refcursor(l_cursor2);

 28             CASE l_col_cnt

 29                     when 1 then

 30                             loop

 31                                     fetch l_refcursor into l_name;

 32                                     exit when l_refcursor%notfound;

 33                                     dbms_output.put_line(' Dname = '||l_name) ;

 34                             end loop;

 35                     when 2 then

 36                             loop

 37                                     fetch l_refcursor into l_name,l_sal;

 38                                     exit when l_refcursor%notfound;

 39                                     dbms_output.put_line(' Ename = '||l_name||

 40                                     ' Salary = '||l_sal);

 41                             end loop;

 42             end case;

 43             close l_refcursor;

 44     end loop;

 45  end;

 46  /

 Dname = ACCOUNTING

 Dname = RESEARCH

 Dname = SALES

 Dname = OPERATIONS

 Ename = CLARK Salary = 2450

 Ename = KING Salary = 5000

 Ename = MILLER Salary = 1300

PL/SQL procedure successfully completed.

rajesh@PDB1>