Dynamic SQL changes in PL/SQL 11G – Oracle Database 11G release 2 (11.2)

New enhancements are added in 11g for dynamic SQL in PL/SQL :

  • SQL statements that are larger than 32 KB are now allowed in native dynamic SQL
  • CLOB can be used for DBMS_SQL.PARSE
  • DBMS_SQL cursor can be converted to REF CURSOR and vice versa
  • Mored data types(including object types, collections) are supported in DBMS_SQL

Support for CLOB in native dynamic

Previous limit 32KB is removed in this version by possibility to use CLOB for dynamic coding. It simplifies coding and makes it less error prone.

declare
  v_sql  CLOB;
begin
  v_sql := 'create table test_tbl(id number)';

  execute immediate v_sql;
end;
/

DBMS_SQL package is also extended and supports CLOB

declare
  v_cursor  integer;
  v_sql     CLOB;
begin
  v_cursor := dbms_sql.open_cursor;
  v_sql := 'select 1 ok from dual';
  dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
  dbms_sql.close_cursor(v_cursor);
end;
/

Conversions between DBMS_SQL and REF_CURSOR

New functions are introduced in DBMS_SQL package for this functionality:

DBMS_SQL.TO_REFCURSOR enables to transform SQL cursor into weak PL/SQL variable REF_CURSOR

DBMS_SQL.TO_REFCURSOR
(p_cursor IN INTEGER)
RETURN SYS_REFCURSOR;

DBMS_SQL.TO_CURSOR_NUMBER enables to transform strong or weak REF CURSOR variable into SQL cursor

DBMS_SQL.TO_CURSOR_NUMBER
(p_ref_cursor IN OUT SYS_REFCURSOR)
RETURN INTEGER;
 declare
  v_sql_cursor    integer;
  v_ref_cursor    sys_refcursor;
  v_sql           CLOB;
  v_ret           number;
  type v_tab_type is table of test_tbl1%rowtype;
  v_tab           v_tab_type;
begin
  /***************************
    SQL CURSOR to REF_CURSOR
   ***************************/  

  ----------------------------------
  -- open SQL CURSOR
  ----------------------------------
  v_sql_cursor := dbms_sql.open_cursor;
  v_sql := 'select * from test_tbl1';
  dbms_sql.parse(v_sql_cursor, v_sql, dbms_sql.native);

  v_ret := dbms_sql.execute(v_sql_cursor);

  ----------------------------------
  -- conversion to REF_CURSOR
  ----------------------------------
  v_ref_cursor := DBMS_SQL.TO_REFCURSOR(v_sql_cursor);

  ----------------------------------
  -- fetch and close
  ----------------------------------
  fetch v_ref_cursor bulk collect into v_tab;

  close v_ref_cursor;

  /***************************
    REF_CURSOR to SQL CURSOR
   ***************************/  

  ----------------------------------
  -- open REF_CURSOR
  ----------------------------------
  open v_ref_cursor for 'select * from test_tbl1';

  ----------------------------------
  -- conversion to REF_CURSOR
  ----------------------------------
  v_sql_cursor := DBMS_SQL.TO_CURSOR_NUMBER(v_ref_cursor);  

  ----------------------------------
  -- fetch and close
  ----------------------------------
  while dbms_sql.fetch_rows(v_sql_cursor) > 0 
  loop
    null;
  end loop;

  dbms_sql.close_cursor(v_sql_cursor);
end;
/

 DBMS_SQL support for abstract types

In this release DBMS_SQL supports following types:

  • opaque types
  • collections
    • nested tables
    • varrays
  • REFs

NOTE – opaque types are types defined by Oracle with internal representation not exposed to public. Such example is XMLType.

Example for XMLType

create table test_tbl
(id xmltype);

declare
  v_sql_cursor    integer;
  v_sql           clob;
  v_xmltype       xmltype;
  v_ret           number;
begin
  ----------------------------------
  -- open SQL CURSOR
  ----------------------------------
  v_sql_cursor := dbms_sql.open_cursor;
  dbms_sql.parse( v_sql_cursor, 
                  'select id from test_tbl', 
                   dbms_sql.native );

  ----------------------------------
  -- columns
  ----------------------------------
  dbms_sql.define_column(v_sql_cursor, 1, v_xmltype);

  v_ret := dbms_sql.execute(v_sql_cursor);

  ----------------------------------
  -- fetch data
  ----------------------------------
  while dbms_sql.fetch_rows(v_sql_cursor) > 0 
  loop
    dbms_sql.column_value(v_sql_cursor, 1, v_xmltype);
  end loop;

  ----------------------------------
  -- close
  ----------------------------------
  dbms_sql.close_cursor(v_sql_cursor);
end;
/

Have a fun 🙂
Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *