SQL>
SQL> CREATE OR REPLACE PROCEDURE CreateDynamicTables(
2 p_Method IN VARCHAR2)
3 AUTHID CURRENT_USER AS
4 v_CreateString1 VARCHAR2(100) :=
5 'CREATE TABLE dbms_sql_table (f1 NUMBER)';
6 v_CreateString2 VARCHAR2(100) :=
7 'CREATE TABLE native_table (f1 NUMBER)';
8 v_Dummy INTEGER;
9 v_CursorID INTEGER;
10 BEGIN
11 IF p_Method = 'DBMS_SQL' THEN
12 v_CursorID := DBMS_SQL.OPEN_CURSOR;
13 DBMS_SQL.PARSE(v_CursorID, v_CreateString1, DBMS_SQL.NATIVE);
14 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
15 ELSE
16 EXECUTE IMMEDIATE v_CreateString2;
17 END IF;
18 END CreateDynamicTables;
19 /
Procedure created.
SQL>
SQL> exec CreateDynamicTables('DBMS_SQL')
PL/SQL procedure successfully completed.
SQL> exec CreateDynamicTables('native')
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>