Oracle: Drop table if exists replacement

February 16, 2010 by Michael

Mysql has a nice “if exists” addition to the drop table statement. If the table to be dropped does not exists, it doesn’t raise an exception but only creates a warning.

In Oracle RDMBS you can emulate this behavior like so:

BEGIN EXECUTE immediate 'drop table INSERT_TABLE_NAME_HERE'; EXCEPTION WHEN others THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/

Ugly, but it works very well.

No comments yet

Post a Comment

Your email is never published nor shared. Required fields are marked *