I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS
construct.
Specifically, whenever I want to drop a table in MySQL, I do something like
DROP TABLE IF EXISTS `table_name`;
This way, if the table doesn't exist, the DROP
doesn't produce an error, and the script can continue.
Does Oracle have a similar mechanism? I realize I could use the following query to check if a table exists or not
SELECT * FROM dba_tables where table_name = 'table_name';
but the syntax for tying that together with a DROP
is escaping me.
You could always catch the error yourself.
It is considered bad practice to overuse this, similar to empty catch()'es in other languages.
Regards
K
And if you want to make it re-enterable and minimize drop/create cycles, you could cache the DDL using dbms_metadata.get_ddl and re-create everything using a construct like this:
declare v_ddl varchar2(4000); begin select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') into v_ddl from dual; [COMPARE CACHED DDL AND EXECUTE IF NO MATCH] exception when others then if sqlcode = -31603 then [GET AND EXECUTE CACHED DDL] else raise; end if; end;
This is just a sample, there should be a loop inside with DDL type, name and owner being variables.The best and most efficient way is to catch the "table not found" exception: this avoids the overhead of checking if the table exists twice; and doesn't suffer from the problem that if the DROP fails for some other reason (that might be important) the exception is still raised to the caller:
ADDENDUM For reference, here are the equivalent blocks for other object types:
Sequence
View
Trigger
Index
Column
Database Link
Materialized View
Type
Constraint
Scheduler Job
User / Schema
Package
Procedure
Function
Tablespace
just wanted to post a full code that will create a table and drop it if it already exists using Jeffrey's code (kudos to him, not me!).
Sadly no, there is no such thing as drop if exists, or CREATE IF NOT EXIST
You could write a plsql script to include the logic there.
http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9003.htm
I'm not much into Oracle Syntax, but i think @Erich's script would be something like this.
With SQL*PLUS you can also use the WHENEVER SQLERROR command:
With
CONTINUE NONE
an error is reported, but the script will continue. WithEXIT SQL.SQLCODE
the script will be terminated in the case of an error.see also: WHENEVER SQLERROR Docs