How to drop table in Oracle procedure?

How to  drop table in Oracle procedure?

CREATE OR REPLACE PROCEDURE deletetable AS
BEGIN
  DROP TABLE step1;
END;
/

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE DELETETABLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PLS-00103: Encountered the symbol "DROP" when expecting one of
         the following:....
apergielAsked:
Who is Participating?
 
bkesarlaConnect With a Mentor Commented:
Hi,

  Use DBMS_SQL Package to execute  DDL Stmts from PL/SQL.

Try the foll :

CREATE OR REPLACE PROCEDURE deletetable AS
  cursor1 INTEGER;
  BEGIN
    cursor1 := dbms_sql.open_cursor;
    dbms_sql.parse(cursor1, 'DROP TABLE <tableName>' ,   dbms_sql.v7);
    dbms_sql.close_cursor(cursor1);
  end;
 /


0
 
tbcoxCommented:
In version 8.1.x you can issue this command in PL/SQL:

EXECUTE IMMEDIATE 'drop table step1';

For prior versions, it's trickier -- DDL is not directly supported in PL/SQL the way DML is.  You would call DBMS_SQL.open_cursor, then DBMS_SQL.parse, then DBMS_SQL.execute.

As Oracle says in the package header of DBMS_SQL:  

  --  NOTE THAT PARSING AND
  --  EXECUTING DDL STATEMENTS CAN CAUSE HANGS!
  --  Currently, the deferred parsing feature of the Oracle  Call Interface
  --  is not used. As a result, statements are parsed immediately. In addition,
  --  DDL statements are executed immediately when parsed. However,
  --  the behavior may
  --  change in the future so that the actual parsing (and execution of DDL
  --  statement) do not occur until the cursor is executed with "execute".
  --  DO NOT RELY ON THE CURRENT TIMING OF THE ACTUAL PARSING!

Those are your only choices, so far as I am aware, for performing DDL in PL/SQL blocks.

 -Tom

0
All Courses

From novice to tech pro — start learning today.