1

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.

  • 2
    You can do this if the instance is only used exclusively by you - Just execute the DDL, then use FLASHBACK DATABASE or similar. To be honest, conditional scripting is better. The likes of rails and web frameworks have led to laziness and less care taken when making database changes. – Philᵀᴹ Jun 13 '14 at 13:49
2

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;
  • 2
    I can't believe it's 2014 and Oracle still doesn't have basic features like transactional DDL and still requires all of these work-arounds. – Colin 't Hart Jun 13 '14 at 21:07
  • Not exactly what I'm looking for (although that doesn't exist), but this does seem like a more elegant approach than the repeated existence checks. – eebbesen Jun 14 '14 at 13:22
  • In Oracle altering an object involves an implied commit. This seems to be by design, not by accident. There are differences in database philosophies across different database vendors. I would probably create the objects in a dev instance the way they are in prod, then do a data pump export of the meta data only. You can then automate the refresh of the schema in dev and script the dropping and reloading of the objects. This way you can run through the test many times before doing it in production. – Gandolf989 Jun 16 '14 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.