0

I have 40 views that are integrated/synced to a work order management system on a weekly basis.

The views are based on GIS tables which are notoriously messy. Over time, the views can end up becoming invalid.

Example:

  • A user deletes or renames a column in an underlying table, but fails to notify I.T. of the change, so the view is not updated accordingly.
  • As a result, the view would become invalid: ORA-04063: view "ROADS_VW" has errors or ORA-00904: "FIELD1": invalid identifier.

I would like to catch & fix invalid views (or fix the underlying data) before the views are synced to the work order management system each week.

Is there a way to get Oracle to notify me about invalid views?

For instance, if the integrations occur on Fridays, then get an email on Thursdays if any of the views are invalid.

  • What do you mean by "compile a view"? And what are the symptoms of "failing to compile"? – mustaccio Oct 13 '19 at 19:25
  • @mustaccio As a novice, I thought that compile meant to select from a view. But it sounds like that was wrong. I'll edit the question. – User1973 Oct 13 '19 at 19:39
  • In computer science, and IT in general, "to compile" commonly means "to convert a program from a human readable source form into machine-executable code". And it still isn't clear what is meant by "views have errors". Can you give an example of such an error? – mustaccio Oct 13 '19 at 19:46
  • @mustaccio Thanks for your patience. I provided some examples in the question. – User1973 Oct 13 '19 at 20:25
  • It is not a matter of GIS world if users change table/view structures. It only depends on internal processes and privileges. It is really strange if people changes their tables but you can't create primary keys, triggers, unique indexes, etc - this does not match. – Wernfried Domscheit Oct 14 '19 at 6:14
2

You can get invalid views with a procedure like this:

DECLARE

    CURSOR InvaliViews AS
    SELECT OBJECT_NAME
    FROM USER_OBJECTS
    WHERE object_type = 'VIEW'
        AND status = 'INVALID';

BEGIN

    FOR aView IN InvaliViews LOOP
        UTL_MAIL.SEND(
            sender => 'noreply@your_domain.com',
            recipients => 'your-email@your_domain.com',
            subject => 'Notification about invalid views',
            message => 'View '||aView.OBJECT_NAME||' is not valid');
    end loop;

end;

You can run such procedure once a week triggered by Scheduler Job

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.