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