Define a procedure to drop a database object : Execute immediate « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Execute immediate

SQL>
SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6       );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE dropit (ittype IN VARCHAR2, itname IN VARCHAR2)
  2  IS
  3  BEGIN
  4     EXECUTE IMMEDIATE 'drop ' || ittype || ' ' || itname;
  5  END;
  6  /

Procedure created.

SQL>
SQL> EXEC dropit('table','product');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>