存储过程失效处理

存储过程失效处理

 

----查询失效的 

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