create or replace procedure p_check_invalid
-- recompile invalid stored program objects
-- CAVEAT: does not take package dependencies
-- into account!
as
CURSOR invalid_prog_obj IS
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID';
rec invalid_prog_obj%ROWTYPE;
status NUMERIC;
BEGIN
set serveroutput on;
DBMS_OUTPUT.enable;
OPEN invalid_prog_obj;
LOOP -- recompile each stored program object
FETCH invalid_prog_obj INTO rec;
EXIT WHEN invalid_prog_obj%NOTFOUND;
DBMS_OUTPUT.put('Recompile ' || rec.object_type ||
' ' || rec.object_name);
DBMS_DDL.alter_compile(rec.object_type, NULL, rec.object_name);
DBMS_OUTPUT.put_line(' SUCCESSFUL'); -- recompile succeeded
END LOOP; -- invalid program objects
CLOSE invalid_prog_obj;
EXCEPTION
WHEN OTHERS THEN
BEGIN
status := SQLCODE;
DBMS_OUTPUT.put_line(' FAILED with ' || SQLERRM(status));
IF (invalid_prog_obj%ISOPEN) THEN
CLOSE invalid_prog_obj;
END IF;
EXCEPTION WHEN OTHERS THEN
NULL; -- do nothing
END;
END;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9417382/viewspace-932276/ ，如需转载，请注明出处，否则将追究法律责任。
