apergiel
asked on
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:....
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:....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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