To drop a table is straightforward in Oracle. One might simply issue a drop table statement. Let us assume we have table HH. When “drop table HH” is fired, the table is removed. However, an error is returned if the stable doesn’t exist. Again: if table HH doesn’t exist and a SQL “drop table HH” is fired, an error is returned: .ORA-00942 is generated. In itsself, this is understable as a non-existent table is attempted to drop. Understably, such error is generated to warn us that something weird happened. In other DMBS, one might use use a statement like “drop if exists table HH”. However, Oracle doesn’t provide us with this facility.
In Oracle, one needs to write a small PL SQL procedure to circumvent the error. The code reads like:
create or replace PROCEDURE DROP_TABLE(NAAM IN STRING) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE '||NAAM; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END DROP_TABLE; /
This procedure can be called by “EXEC DROP_TABLE(‘HH’);”.
The procedure calls the statement DROP TABLE HH. Howver if HH doesn’t exist, an error is raised. Howver such error is handled as an exception that doesn’t lead to an error generated by the procedure.