Oracle: Script to list the invalid objects in the DB

oracle-list-invalid-objects.sql

-- List the invalid objects from the Oracle DB

DECLARE
    iCount INTEGER := 0;
    objects VARCHAR2(4000 CHAR);
    new_line VARCHAR2(2):= CHR(13) || CHR(10);
BEGIN
    FOR usr_objects IN
    (
        SELECT object_name, object_type, status
        FROM    user_objects
        WHERE   object_type IN ('VIEW','PROCEDURE', 'FUNCTION', 'PACKAGE') AND STATUS = 'INVALID'
        ORDER BY object_type, object_name
    )
    LOOP
        objects := objects || new_line || usr_objects.object_name || ' [' || lower(usr_objects.object_type) || ']';
        iCount := iCount + 1;
    END LOOP;
    IF (iCount > 0) THEN
        raise_application_error (-20001, 'There are ' || iCount || ' compilation errors in the schema.' || new_line || objects);
    END IF;
END;