Recompile (not just) invalid objects

I

I use object recompilation on daily basis mainly to check that I didn't broke other objects. You can find latest version on my github account under Kvido project - procedures/recompile.sql.

Arguments should be self explanatory:

    in_filter_type      VARCHAR2    := '%',
    in_filter_name      VARCHAR2    := '%',
    in_code_type        VARCHAR2    := 'INTERPRETED',
    in_scope            VARCHAR2    := 'IDENTIFIERS:ALL, STATEMENTS:ALL',
    in_warnings         VARCHAR2    := 'ENABLE:SEVERE, ENABLE:PERFORMANCE',
    in_optimize         NUMBER      := 2,
    in_ccflags          VARCHAR2    := NULL,
    in_invalid_only     BOOLEAN     := TRUE

Usage

SET SERVEROUTPUT ON SIZE UNLIMITED
BEGIN
    recompile();
END;
/

You should see that it allows you to change PL/SQL scope of all objects so you can utilize user_identifiers and user_statements views later. It also allows you to change conditional compilation flags. I will wrote some articles about both features later.

You should also explore user_plsql_object_settings and user_stored_settings views.

Fix scope

You can just fix scope with this script:

DECLARE
    in_code_type    CONSTANT VARCHAR2(100) := 'INTERPRETED';
    in_scope        CONSTANT VARCHAR2(100) := 'IDENTIFIERS:ALL, STATEMENTS:ALL';
BEGIN
    EXECUTE IMMEDIATE
        'ALTER SESSION SET PLSQL_CODE_TYPE = ' || in_code_type;
    EXECUTE IMMEDIATE
        'ALTER SESSION SET PLSCOPE_SETTINGS = ''' || in_scope || '''';
    --
    FOR c IN (
        SELECT s.*
        FROM user_plsql_object_settings s
        WHERE (s.plsql_code_type        != in_code_type
                OR s.plscope_settings   != in_scope)
            AND s.name                  != 'RECOMPILE'      -- ignore this procedure
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(c.type || ' ' || c.name);
        EXECUTE IMMEDIATE
            'ALTER ' || REPLACE(c.type, ' BODY') || ' ' || c.name || ' COMPILE' ||
            CASE WHEN c.type LIKE '% BODY' THEN ' BODY' END;
    END LOOP;
END;
/