Re: How to drop an nonexist table without the error message?

>CREATE OR REPLACE TRIGGER Drop_Table (The_Table_Name IN VARCHAR2)
>RETURN BOOLEAN IS
>BEGIN
> -- Only drop a table if it doesn't exist.
> IF Table_Exists(The_Table_Name) THEN
> DROP TABLE Table_Name;
> END IF;
> -- The table should not exist anymore.
> RETURN NOT Table_Exists(The_Table_Name);
>END Drop_Table;
>/
>

OK, I think I am moving in the right direction.

CREATE OR REPLACE TRIGGER Drop_Table (The_Table_Name IN VARCHAR2) RETURN NUMBER IS
BEGIN

• Only drop a table if it doesn't exist. IF Table_Exists(The_Table_Name) THEN DBMS_UTILITY.EXEC_DDL_STATEMENT ('DROP TABLE ' || The_Table_Name || ';'); END IF;
• The table should not exist anymore. IF Table_Exists(The_Table_Name) THEN RETURN 1; END IF; RETURN 0; END Drop_Table;

However, when trying

DROP TABLE A;
CREATE TABLE A (A SMALLINT);
SELECT Drop_Table('A') FROM Dual;

I get:

Table dropped.

Table created.

SELECT Drop_Table('A') FROM Dual

       *
ERROR at line 1:

ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "SYS.DBMS_UTILITY", line 332
ORA-06512: at "BRIAN.DROP_TABLE", line 6
ORA-06512: at line 1

Brian Received on Wed May 10 2000 - 00:00:00 CDT