grant executee on sys.utl_recomp to system;

CREATE OR REPLACE PROCEDURE SYSTEM.Custom_Recompile_Invalid_Objects (schema in VarChar2) as
begin
  sys.utl_recomp.recomp_serial(schema);
end;
/
grant execute on system.Custom_Recompile_Invalid_Objects to ....

PROCEDURE COMPILE_SCHEMA (SCHEMA         VARCHAR2,
                            COMPILE_ALL    BOOLEAN DEFAULT TRUE,
                            REUSE_SETTINGS BOOLEAN DEFAULT FALSE) IS
    NOT_EXIST_OR_NO_PRIV EXCEPTION;
    PRAGMA EXCEPTION_INIT(NOT_EXIST_OR_NO_PRIV, -20000);

    COMMIT_DISABLED EXCEPTION;
    PRAGMA EXCEPTION_INIT(COMMIT_DISABLED, -34);

    IN_DIST_TX EXCEPTION;
    PRAGMA EXCEPTION_INIT(IN_DIST_TX, -2074);

    IN_TRIGGER EXCEPTION;
    PRAGMA EXCEPTION_INIT(IN_TRIGGER, -4092);

    CURSOR INTERESTING_OBJS_C IS
      SELECT O.OBJ#
        FROM OBJ$ O, USER$ U
        WHERE U.USER# = O.OWNER#
$IF UTL_IDENT.IS_ORACLE_SERVER $THEN
              AND O.REMOTEOWNER IS NULL
$ELSE
  
$END
              AND U.NAME  = SCHEMA
              AND O.TYPE# IN (7, 8, 9, 11, 12)
$IF UTL_IDENT.IS_ORACLE_SERVER $THEN
              
              AND (BITAND(O.FLAGS, 128) = 0);
$ELSE
  ;
$END

  BEGIN

    
    
    
    
    IF (UPPER(SCHEMA) = 'SYS') THEN
      RAISE_APPLICATION_ERROR(-20001, 'Cannot recompile SYS objects');
    END IF;

    IF (COMPILE_ALL) THEN
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      FOR INTERESTING_OBJ IN INTERESTING_OBJS_C LOOP
        BEGIN
          INVALIDATE(INTERESTING_OBJ.OBJ#, 
                     CASE WHEN REUSE_SETTINGS = TRUE THEN 'REUSE SETTINGS'
                                                     ELSE ' '
                     END);
        EXCEPTION
          WHEN INV_NOT_EXIST_OR_NO_PRIV THEN
            RAISE NOT_EXIST_OR_NO_PRIV;
          WHEN OTHERS THEN
            NULL;  
        END;
      END LOOP;
    END IF;

    BEGIN
      UTL_RECOMP.RECOMP_SERIAL(SCHEMA);
    EXCEPTION
      WHEN NOT_EXIST_OR_NO_PRIV THEN
        
        RAISE;
      WHEN COMMIT_DISABLED OR IN_DIST_TX OR IN_TRIGGER THEN
        RAISE;
      WHEN OTHERS THEN
        NULL;  
    END;

  END;