同 ID 147574 內容,此處提供更規範的 PL/SQL 封裝版本,適合納入定時任務:
CREATE OR REPLACE PROCEDURE ZL_COMPILE_INVALID_PROCEDURE AS
V_SQL VARCHAR2(200);
BEGIN
FOR X IN (SELECT OBJECT_NAME FROM ALL_OBJECTS
WHERE STATUS = 'INVALID' AND OBJECT_TYPE = 'PROCEDURE') LOOP
V_SQL := 'ALTER PROCEDURE ' || X.OBJECT_NAME || ' COMPILE';
BEGIN
EXECUTE IMMEDIATE V_SQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FAILED: ' || X.OBJECT_NAME || ' - ' || SQLERRM);
END;
END LOOP;
END;
/
-- 執行前
SET SERVEROUTPUT ON
EXEC ZL_COMPILE_INVALID_PROCEDURE;
優勢:單次編譯失敗不中斷整體流程,輸出失敗對象與錯誤碼,便於事後分析。可擴展支援 FUNCTION、PACKAGE、TRIGGER 等對象類型。