17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PROCEDURE compile_all_obj AS
CURSOR cur_invalid_objects IS
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID';
rec_columns cur_invalid_objects%ROWTYPE;
err_status NUMERIC;
BEGIN
dbms_output.enable(10000);
OPEN cur_invalid_objects;
LOOP
FETCH cur_invalid_objects
INTO rec_columns;
EXIT WHEN cur_invalid_objects%NOTFOUND;
dbms_output.put_line('Recompiling ' || rec_columns.object_type || ' ' ||
rec_columns.object_name);
dbms_ddl.alter_compile(rec_columns.object_type,
NULL,
rec_columns.object_name);
END LOOP;
CLOSE cur_invalid_objects;
EXCEPTION
WHEN OTHERS THEN
BEGIN
err_status := SQLCODE;
dbms_output.put_line(' Recompilation failed : ' ||
SQLERRM(err_status));
IF (cur_invalid_objects%ISOPEN) THEN
CLOSE cur_invalid_objects;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;