存储过程失效处理

----查询失效的
SELECT OBJECT_NAME, TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS'), STATUS
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE');
------重新编译对象的语法
ALTER OBJECT_TYPE OBJECT_NAME COMPILE;
-----当然，上面两个步骤也可以通过一条语句完成
DECLARE
V1 VARCHAR2(500);
BEGIN
FOR K IN (SELECT OBJECT_TYPE, OBJECT_NAME
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')) LOOP
V1 := 'alter ' || K.OBJECT_TYPE || ' ' || K.OBJECT_NAME || ' compile';
EXECUTE IMMEDIATE V1;
END LOOP;
END;
------ 查出无效的存储过程并编译 点击(此处) 折叠或打开
SELECT 'ALTER PROCEDURE ' || OBJECT_NAME || ' compile;'
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE = 'PROCEDURE';