存储过程失效处理

----查询失效的
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';