Get notification about invalid views

Question

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.

Answer 1

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