Drop a table in a procedure [duplicate]

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?

Answer 1

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;

Answer 2

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

Answer 3

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;