On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, September 3, 2008

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 */

No comments:

Post a Comment