Drop If Exists

This is one way to issue a drop command and not have an error message posted when there is no table to drop.

The small test script shown below is the query that will call 'DROP.SQL' when a table is to be dropped. It creates a table, and then calls Drop with the variable &1 set equal to the table name to be dropped. It immediately tries it again to prove that no error message will be posted.

clear screen
Create Table TestTbl (num number);
@drop TestTbl

@drop TestTbl

---

This is the query that will generate the drop statement, and then run it:

Drop.SQL

Set Term Off                                    /* Hide what's going on */
Store Set InitSets Replace                      /* Save 'Sets' so they can be reset */
Set Pagesize 0 Feedback Off Verify Off Echo Off /* Turn off to create clean spool file */

Spool Drop_if_Exists.sql Replace                /* This will be run after it is generated */

Select 'Drop Table '||Table_Name||';'           /* See the spool file to view statment created */
  From User_Tables
 Where Table_Name = Upper('&1');                /* Where table name = the one passed in */

Spool Off

@initsets                                       /* Reset their 'sets' */
@Drop_If_Exists                                 /* Execute the spool file created */