Mega Code Archive
Illustrates the interaction of roles and dynamic SQL
<![CDATA[
SQL>
SQL> DECLARE
2 v_CreateString1 VARCHAR2(100) :=
3 'CREATE TABLE dbms_sql_table (f1 NUMBER)';
4 v_CreateString2 VARCHAR2(100) :=
5 'CREATE TABLE native_table (f1 NUMBER)';
6 v_Dummy INTEGER;
7 v_CursorID INTEGER;
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10 DBMS_SQL.PARSE(v_CursorID, v_CreateString1, DBMS_SQL.NATIVE);
11 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
12
13 EXECUTE IMMEDIATE v_CreateString2;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> desc dbms_sql_table
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------
F1 NUMBER
SQL> desc native_table
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------
F1 NUMBER
SQL>
SQL> DROP TABLE dbms_sql_table;
Table dropped.
SQL> DROP TABLE native_table;
Table dropped.
SQL>
]]>