Can I run DDL in pretend mode in Oracle

Question

I have a script to drop several objects in the database (including queues) -- when it partially fails I need to recreate the objects that have already been dropped to continue testing the script. For example, if the queue doesn't exist, the script fails when trying to stop it. I want to use this for Rails migrations: new dev and test schemas won't have the queues or database links that the teardown script attempts to drop, for example.

Many frameworks allow one to run commands in a test or pretend mode (e.g. rails generate -p). Is there any similar functionality in Oracle for testing commands that one cannot roll back?

I understand I can do conditional checking for the presence/state of database objects, but would prefer to avoid creating a bunch of checks and dynamic SQL if there's a better way.

Answer 1

No need to check for existence. Just wrap your statement with an exception handler that ignores away any ORA-00955 it encounters (or modify for whatever exception number you want to ignore):

DECLARE
  ObjectExists EXCEPTION;
  PRAGMA EXCEPTION_INIT(ObjectExists,-955);
  sql_stmt VARCHAR2(100) := 'create table my_table (column_1 int)';
BEGIN
  EXECUTE IMMEDIATE sql_stmt;
-- Ignore ORA-955 errors (object already exists)
EXCEPTION WHEN ObjectExists THEN NULL;
END;