1

I would like to perform a DROP VIEW IF EXISTS... command on an Oracle DB as it can be done on a MySQL database. How can this be achieved?

Improve this question
  • 1
    Oracle's own admin scripts typically just issue a DROP on the object and have notes in the appropriate docs that errors on DROP can be ignored. Or the DBA quickly figures out on his own that if you get a 'NOT FOUND' type of error when trying to DROP and object, that the intent was to make the object not exist, so you have still achieved the desired end state. – EdStevens Aug 19 '19 at 17:24
  • Yes, but on database deployment SQL scripts that are meant to be able to be executed multiple times, it is valuable to have the IF EXISTS sort of behavior. – Daniel Gray Aug 19 '19 at 17:42
  • Lot's of oracle's own admin scripts are meant/expected to be executed multiple times. That doesn't change anything. Now, you might consider it to be 'cleaner' or 'desireable' to not have to deal with the error emanating from what turns out to be - in a particular execution - a superfluous DROP, but the perceived "value" of such is more in the eye of the beholder. – EdStevens Aug 19 '19 at 19:59
3

An alternate method:

  DECLARE
     dne_942    EXCEPTION;
     PRAGMA EXCEPTION_INIT(dne_942, -942);
  BEGIN
     EXECUTE IMMEDIATE 'DROP VIEW my_view';
  EXCEPTION
     WHEN dne_942 THEN
        NULL; -- if it doesn't exist, do nothing .. no error, nothing .. ignore.
  END;
  /

This has the added benefit of not needing to query the data dictionary .. just try to fire the drop .. and if it doesn't exist .. that's fine .. no real issue.

Improve this answer
  • Looks a bit scary/obscure but I do agree that it's good that there is no duplication of the view name. Good to know of other options! – Daniel Gray Aug 19 '19 at 17:49
  • 1
    @WernfriedDomscheit And that's no different than any other solution ... you add whatever additional error handling you need. – Ditto Aug 19 '19 at 19:50
1

If you're using sqlplus (I use it for a lot of batch processes) you can just ignore the error:

WHENEVER SQLERROR CONTINUE;
DROP VIEW my_view;
WHENEVER SQLERROR EXIT 1;

After you ignore the possible error from dropping the view, you might want to turn it back to the regular behavior (exit on error). The 1 is optional, in case you want to raise the errorlevel.

Improve this answer
0

There is no way to do it in a single command, but it can be achieved with a small PL/SQL block as follows:

DECLARE cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO cnt FROM user_views WHERE view_name = 'MY_VIEW';
  IF cnt <> 0 THEN
    EXECUTE IMMEDIATE 'DROP VIEW my_view';
  END IF;
END;
/
Improve this answer

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.