DBMS_SQL.NATIVE; : dbms_sql « System Packages « Oracle PL / SQL

DBMS_SQL.NATIVE;

  

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>

Related examples in the same category

1.	First DBMS_SQL Example
2.	Pass a query statement to a stored procedure
3.	Use dbms_sql.open_cursor create a cursor
4.	Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.	Use dbms_sql.bind_array to bind array to a cursor
6.	Use dbms_sql.parse to bind a select statement to a cursor
7.	Call dbms_sql.describe_columns2 to get info for a column
8.	Non-Query DML and DDL Statements
9.	Use DBMS_SQL with the RETURNING clause.
10.	DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
11.	Dynamic sql statement with variable binding
12.	Dynamic select statement and row fetch
13.	Execute PL/SQL block
14.	DBMS_SQL package: dynamic SQL
15.	Pass where clause to a stored procedure
16.	Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
17.	Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
18.	drop the supplied table using dynamic SQL.
19.	DBMS_SQL.BIND_VARIABLE_CHAR
20.	illustrate the importance of setting out_value_size.
21.	Calling a function which uses dynamic SQL from within an SQL statement.
22.	Illustrates the interaction of roles and dynamic SQL.
23.	illustrates a DDL statement which is built dynamically from the procedure parameters.
24.	Use dbms_sql to process query, cursor and value
25.	Use dbms_sql.bind_variable to bind variable
26.	dbms_sql.varchar2_table type variable