Oracle Compile Object

SET DEFINE ON
SET ESCAPE ON
SET VERIFY OFF

DEFINE OWNER = &1;

SET SERVEROUTPUT ON

declare 
    v_query        VARCHAR2(500);
    v_object_name  VARCHAR2(50);
    v_object_type  VARCHAR2(50);
    v_compile      VARCHAR2(200);
    ref_views      SYS_REFCURSOR;
BEGIN
    v_query := 'SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE ' ||
      'OBJECT_TYPE IN (''VIEW'', ''FUNCTION'', 'PRODUCURE', ''TRIGGER'',''PACKAGE BODY'') ' ||
      'AND OWNER = ''&EPSSOWNER'' ' ||
      'AND STATUS = ''INVALID''';
    OPEN ref_views FOR v_query;
    LOOP
    FETCH ref_views INTO v_object_name, v_object_type;
    EXIT WHEN ref_views%NOTFOUND;
      IF v_object_type ='PACKAGE BODY' THEN
         v_compile := 'ALTER PACKAGE &OWNER' ||'.'|| v_object_name || ' COMPILE BODY';
      ELSE   
         v_compile := 'ALTER ' || v_object_type || ' &OWNER' ||'.'|| v_object_name || ' COMPILE';
      END IF;
        DBMS_OUTPUT.PUT_LINE(v_compile);
        BEGIN
          EXECUTE IMMEDIATE v_compile;
        EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Error exists in ' || v_object_type || ': ' || v_object_name);
        END;  
    END LOOP;
    CLOSE ref_views;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('EXCEPTION:');
  DBMS_OUTPUT.PUT_LINE(SQLERRM || CHR(13) || CHR(10) || v_compile);
  --DBMS_OUTPUT.PUT_LINE('All changes on this table has been rollback.');
END;
/

PROMPT RECOMPILE_OBJECT - Completed.

 

有时候修改一些Oracle的object会导致编译的问题,可以利用这个脚本来重新编译