1

What's going on here?

DECLARE
  V_COUNT NUMBER(10) := 0;
BEGIN

  SELECT COUNT(*) INTO V_COUNT FROM USER_VIEWS WHERE VIEW_NAME = 'DBO$EVT_APP';

  IF V_COUNT > 0 THEN
    DROP VIEW DBO$EVT_APP;
  END IF;

END;

I'm getting the following error:

Error report:
ORA-06550: line 9, column 5:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following: ...

This looks like valid SQL to me. This is my exact code, pasted.

Ray Bae
  • 89
  • 1
  • 4
  • 10
  • 4
    You can't use DDL in PL/SQL directly you need to use dynamic SQL. –  Jan 26 '16 at 18:18

1 Answers1

1
DECLARE
  V_COUNT NUMBER(10) := 0;

    BEGIN

      SELECT COUNT(*) INTO V_COUNT FROM USER_VIEWS WHERE VIEW_NAME = 'DBO$EVT_APP';

      IF V_COUNT > 0 THEN
        execute immediate 'DROP VIEW DBO$EVT_APP';
      END IF;

    END;
Piotr Siekierski
  • 484
  • 2
  • 7
  • Thanks! The dynamic sql isn't dropping the view though! When I copy that which is in quotes and run it with an added semicolon, it drops the view! But running it dynamically won't. – Ray Bae Jan 26 '16 at 18:35