檢查不可用的物件（轉）

檢查不可用地物件。[@more@]

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;
/