Geek asks and answers

Drop table in procedure

Possible duplicate:
Oracle: if the table exists.
Disable the table if it exists.

I try 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;

Error:

Error (8.1): PLS-00103: the "END" character is encountered, expecting one of the following: (start 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 save set sql point execute commit forall merge pipe purge.

Do you know what I am doing wrong?

+5
source share
3 answers

you will 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;
+11
source

DROP DROP. DDL- PL/SQL EXECUTE IMMEDIATE.

+2

This will not allow you to directly use the DDL structure inside the PLSQL procedure. To execute DDL you need to use the instruction Execute Immediate.

Use the following code:

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;
+1
source

More articles:


All Articles