Drop a table in a procedure

Question

Possible Duplicate:
Oracle: If Table Exists
Drop table if it exists

I'm trying to create this procedure but I get an error.

CREATE OR REPLACE PROCEDURE SP_VEXISTABLA(NOMBRE IN VARCHAR2)
IS
CANTIDAD NUMBER(3);
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM ALL_OBJECTS WHERE OBJECT_NAME = NOMBRE;
IF (CANTIDAD >0) THEN
    DROP TABLE NOMBRE;
END IF;
END;

The error is:

Error(8,1): PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge.

Do you know what am I doing wrong?

It's not allowed to use DDL statements (`DROP TABLE NOMBRE;` in this case ) inside a PL/SQL block. To execute a DDL statement inside a PL/SQL block you would have to use dynamic SQL: `execute immediate 'DROP TABLE NOMBRE';` for instance. — Nick Krasnov, Jan 28 '13 at 15:04
You should be aware that by querying ALL_OBJECTS you might find an object that is neither a table nor in your own schema. If you only want to query for tables in your own schema then use USER_TABLES. Also, if TABLE_LOCK is "DISABLED" or DROPPED is "YES" then the drop will fail. — David Aldridge, Jan 28 '13 at 15:25

score 20 · Accepted Answer · answered Jan 28 '13 at 15:15

20

you'd need to change your procedure to:

CREATE OR REPLACE PROCEDURE SP_VEXISTABLA(NOMBRE IN VARCHAR2)
IS
CANTIDAD NUMBER(3);
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM USER_TABLES WHERE TABLE_NAME = NOMBRE;
IF (CANTIDAD >0) THEN
    execute immediate 'DROP TABLE ' || NOMBRE;
END IF;
END;

answered Jan 28 '13 at 15:15

DazzaL

21,638
3
49
57

DazzaL@I'm using same stored procedure and getting error BEGIN procedure SP_VEXISTABLA('xyz'); END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "PROCEDURE" was ignored. – Aug 18 '15 at 17:27

score 3 · Answer 2 · edited Jan 28 '13 at 15:57

3

You cannot DROP tables in procedure using the DROP command. You need to use EXECUTE IMMEDIATE to run DDL commands in PL/SQL.

edited Jan 28 '13 at 15:57

Ben

51,770
36
127
149

answered Jan 28 '13 at 15:13

Art

5,616
1
20
22

score 2 · Answer 3 · edited Feb 26 '18 at 15:02

It will not allow you use directly DDL statament inside the PLSQL Procedure. You need to use Execute Immediate statement in order to execute the DDL.

Use the code below:

CREATE OR REPLACE PROCEDURE SP_VEXISTABLA(Table_nameIN VARCHAR2)
IS
CANTIDAD integer;
BEGIN
   SELECT COUNT(*) INTO CANTIDAD FROM USER_TABLES WHERE TABLE_NAME = Table_name;
   DBMS_OUTPUT.PUT_LINE(CANTIDAD);
   IF (CANTIDAD >0) THEN
      DBMS_OUTPUT.PUT_LINE(Table_name);
      execute immediate 'DROP TABLE ' || Table_name;
   END IF;
END;

Drop a table in a procedure

3 Answers3