drop the supplied table using dynamic SQL. : dbms_sql « System Packages « Oracle PL / SQL






  1. Oracle PL / SQL
  2. System Packages
  3. dbms_sql

drop the supplied table using dynamic SQL.

  
SQL>
SQL> CREATE OR REPLACE PROCEDURE DropTable(p_Table IN VARCHAR2) AS
  2    sqlString VARCHAR2(100);
  3    v_Cursor BINARY_INTEGER;
  4    v_ReturnCode BINARY_INTEGER;
  5  BEGIN
  6    sqlString := 'DROP TABLE ' || p_Table;
  7
  8    v_Cursor := DBMS_SQL.OPEN_CURSOR;
  9
 10    DBMS_SQL.PARSE(v_Cursor, sqlString, DBMS_SQL.NATIVE);
 11    v_ReturnCode := DBMS_SQL.EXECUTE(v_Cursor);
 12
 13    DBMS_SQL.CLOSE_CURSOR(v_Cursor);
 14  END DropTable;
 15  /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE DropTable(p_Table IN VARCHAR2) AS
  2    sqlString VARCHAR2(100);
  3  BEGIN
  4    sqlString := 'DROP TABLE ' || p_Table;
  5
  6    EXECUTE IMMEDIATE sqlString;
  7  END DropTable;
  8  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
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.NATIVE;
11.DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
12.Dynamic sql statement with variable binding
13.Dynamic select statement and row fetch
14.Execute PL/SQL block
15.DBMS_SQL package: dynamic SQL
16.Pass where clause to a stored procedure
17.Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
18.Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
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