Run DDL in pretend mode in Oracle

ddloracle

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.

Best Answer

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;

Related Solutions

Undo DDL statement on View in Oracle

Depending on the version of Oracle and whether you have the appropriate privileges (FLASHBACK ANY TABLE) and if the change was relatively recent,

SELECT text
  FROM dba_views AS OF TIMESTAMP( systimestamp - interval '1' hour )
 WHERE owner = <<owner of view>>
   AND view_name = <<name of view>>

will give you the text of the view definition as of an hour ago. You can obviously adjust the flashback time to a timestamp just before the DDL you want to undo.

Oracle DDL – Impact on Other Transactions

This article explains how DDL statements work and how they need exclusive table locks, which plays out nicely when there are transactions running against the table. To summarize:

If the table cannot be locked due to another transaction having a lock on the table

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

would be thrown. If the DDL manages to acquire the lock, obviously, any other transaction will block at the point where it needs a lock on the table, until the DDL finishes.

Additionally, since 11g, it is possible to enqueue DDL statements in FIFO order with other statements to get a lock on a table:

declare
begin
      -- We really want to drop that column and so wait for 1800 seconds.
      execute immediate 'alter session set ddl_lock_timeout=1800';
      execute immediate 'alter table t1 drop column n5 ';
exception
  when others then
     dbms_output.put_line(' No luck ');
     dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm );
end;
/

In older versions of Oracle, it could be a challenge to perform this task on a heavily used table.

Best Answer

Related Solutions

Undo DDL statement on View in Oracle

Oracle DDL – Impact on Other Transactions

Related Question