Drop view if exists in Oracle SQL

Question

I'm new to the Oracle database system. What is the equivalent of the below SQL statement in Oracle 12c?

DROP VIEW IF EXIST <view_name>

Ori Marko · Accepted Answer · 2020-07-19T07:13:08.840

2

Except from simply calling DROP VIEW, you can write a procedure that checks before:

BEGIN
 FOR i IN (SELECT null FROM user_views WHERE view_name = 'BOOKS_emp') LOOP
   EXECUTE IMMEDIATE 'DROP VIEW books_emp';
 END LOOP;
END;
/

edited Jul 19 '20 at 07:13

answered Sep 09 '19 at 10:45

Ori Marko

56,308
23
131
233

score 0 · Answer 2 · answered Sep 09 '19 at 10:48

0

What you've written is working in 12c without any issue. Also, you can simply use,

DROP VIEW <VIEW_NAME>;

answered Sep 09 '19 at 10:48

Chamithra Thenuwara

399
6
19

score 0 · Answer 3 · edited Jul 19 '20 at 05:48

0

You can do something via a procedure, but your query is irrelevant when you can run drop view viewname. It can straightaway show you if it exists or not.

  Create or Replace Procedure
      dropifexists(viewname IN
         varchar(50))
      AS
          Declare
          flag number(10);

      Begin
          SELECT count(*) into flag FROM
              user_views
              WHERE view_name = viewname;

          If(flag>0)
              Drop View viewname;
          Else
             dbms_output.putline('View already
             exists')
          End if;
      End;
      /

And, once created, you can call the same procedure:

   begin
       dropifexists('viewname' ) ;
   end;
   /

edited Jul 19 '20 at 05:48

Peter Mortensen

30,738
21
105
131

answered Sep 09 '19 at 11:06

Himanshu

3,830
2
10
29

You also might want to explain how to use it.. – Raymond Nijland Sep 09 '19 at 11:07
Yes @Raymond ive added the code to call the procedure. Thanks! – Himanshu Sep 09 '19 at 11:29
2

`DROP VIEW` would need to be dynamic SQL. – Justin Cave Sep 09 '19 at 13:49

score -1 · Answer 4 · edited Jul 19 '20 at 05:50

Are you sure you want to replace this functionality 1:1? Normally, you'd do a DROP VIEW IF EXISTS in SQL Server because until recently, SQL Server didn't have a CREATE OR ALTER VIEW option. So your promotion scripts would drop the view if it existed and then have a CREATE VIEW statement so it could be run multiple times without a problem.

Oracle has always (well, at least the past couple of decades) supported a CREATE OR REPLACE VIEW statement, so your Oracle scripts would normally just contain a CREATE OR REPLACE VIEW statement. There wouldn't normally be any need to try to drop the view first.

You'd only want to drop the view if it was an obsolete object that you wanted to be removed permanently, but in that case you'd know that the view exists, so it wouldn't make a lot of sense to check for existence first.

it makes sense e.g. on db structure/view updates/releases where things may get obsolete — Andreas Covidiot, Dec 08 '22 at 07:29

Drop view if exists in Oracle SQL

4 Answers4

Linked