Native Dynamic SQL x DBMS_SQL

[ Oracle PL/SQL ]

With Oracle 11g:

• Native dynamic SQL and DBMS_SQL package now support statments larger than 32Kb.
• EXECUTE IMMEDIATE, OPEN-FOR and DBMS_SQL.PARSE accept SQL statements in the form of CLOBs.

• DBMS_SQL.TO_REFCURSOR converts a DBMS_SQL cursor ID into a REF CURSOR.
• DBMS_SQL.to_cursor_number converts a REF CURSOR into a DBMS_SQL cursor ID.

DBMS_SQL.TO_REFCURSOR: From Cursor ID => to REF CURSOR.

set serveroutput on 
declare 
 lcursor number;                       -- for DBMS_SQL Cursor ID
 lreturn number;                       -- for DBMS_SQL Cursor ID
 
 lref_cursor sys_refcursor;            -- for REF CURSOR
 type        t_emptab is table of employees%rowtype;
 lemp_tab    t_emptab;

begin
  lcursor := dbms_sql.open_cursor;
  dbms_sql.parse(lcursor, 
                'Select * from employees',
                 DBMS_SQL.NATIVE);
  lreturn := dbms_sql.execute(lcursor);
  
                                        -- convert from dbms_sql Cursor ID to a REF CURSOR
  lref_cursor := dbms_sql.to_refcursor(lcursor);
  fetch lref_cursor bulk collect into lemp_tab;
  dbms_output.put_line('Employee count: '||lemp_tab.count);
  close lref_cursor;
end;
/
anonymous block completed
Elapsed: 00:00:00.050
Employee count: 107

DBMS_SQL.to_cursor_number: from REF CURSOR => to DBMS_SQL cursor ID.

declare 
  lref_cursor sys_refcursor;
  lcursor number;
  lcount  number := 0;
begin 
  OPEN lref_cursor FOR 'select * from employees';
                                          -- convert from REF CURSOR to a dbms_sql Cursor ID
  lcursor := dbms_sql.to_cursor_number(lref_cursor);

  while dbms_sql.fetch_rows(lcursor) > 0 LOOP
    lcount := lcount + 1;
  end loop;
  dbms_output.put_line('Employee count: '|| lcount);
  dbms_sql.close_cursor(lcursor);
end;
/
anonymous block completed
Elapsed: 00:00:00.024
Employee count: 107