Oracle DBMS_SQL2 Dynamic SQL

Oracle DBMS_SQL2 Version 11.2.0.3

General Information

Note

Interesting package but I wouldn't suggest having too great an expectation it will exist in 12cR1. Based on the name it may be the new container for extending DBMS_SQL functionaltiy or it may be Oracle management's way of telling the developers they didn't deliver on time like they were supposed to.

AUTHID

CURRENT_USER

Dependencies

DBMS_SQL

DBMS_SYS_SQL

First Available

11.2.0.3

Security Model

Owned by SYS with EXECUTE granted to PUBLIC

Source

{ORACLE_HOME}/rdbms/admin/prvtsql2.plb

Subprograms

EXPAND_SQL_TEXT

PARSE

EXPAND_SQL_TEXT

Undocumented but fascinating

dbms_sql2.expand_sql_text(
input_sql_text IN CLOB,
output_sql_text OUT CLOB);

set serveroutput on

DECLARE
iclob CLOB := 'SELECT srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id FROM serv_inst i WHERE s.srvr_id = i.srvr_id)';
oclob CLOB;
BEGIN
dbms_sql2.expand_sql_text(iclob, oclob);
dbms_output.put_line('Input: ' || iclob);
dbms_output.put_line('Output: ' || oclob);
END;
/

--Note the transformation of the subquery

PARSE

Undocumented

Overload 1

dbms_sql2.parse(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);

TBD

Overload 2

dbms_sql2.parse(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);

TBD

Overload 3

dbms_sql2.parse(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);

TBD

Overload 4

dbms_sql2.parse(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2s,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);

TBD