CREATE OR REPLACE PROCEDURE compile_invalid_procedures(p_owner VARCHAR2) AS
--编译某个用户下的无效存储过程
--参数:p_owner 所有者
str_sql VARCHAR2(200);
BEGIN
FOR invalid_procedures IN (SELECT object_name
FROM all_objects
WHERE status = 'INVALID'
AND object_type = 'PROCEDURE'
AND owner = upper(p_owner)) LOOP
str_sql := 'alter procedure ' || invalid_procedures.object_name ||
' compile';
BEGIN
EXECUTE IMMEDIATE str_sql;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
END LOOP;
END compile_invalid_procedures;