查找和编译失效PL/SQL对象

SET  ECHO  OFF
SET  heading  OFF
SET  linesize  350
SET   FEED  OFF
SET  TRIMS  ON
SET  NEWP NONE

SPOOL e: ecompile.SQL

SELECT   ' alter  ' || OBJECT_TYPE || '   ' || OBJECT_NAME || '  COMPILE; ' || CHR( 10 ) || ' SHOW ERROR '   FROM  user_objects  WHERE  status = ' INVALID '
AND  OBJECT_TYPE  IN  ( ' PROCEDURE ' , ' FUNCTION ' , ' PACKAGE ' , ' PACKAGE_BODY ' );

SPOOL  OFF ;

START e: ecompile.SQL;  -- 执行这个SQL语句


-- ####################################################################--
-- #查找失效对象#
-- ##############
-- SELECT OBJECT_TYPE,OBJECT_NAME FROM user_objects 
-- WHERE status='INVALID' AND OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE_BODY');


-- ####################################################################--
-- #编译失效PL/SQL对象的函数
-- #########################
-- DBMS_DDL.ALTER_COMPILE(TYPE VARCHAR2,SCHEMA VARCHAR2,name VARCHAR2);
-- TYPE: 'PROCEDURE','FUNCTION','PACKAGE','PACKAGE_BODY'
-- eg：DBMS_DDL.ALTER_COMPILE('PROCEDURE',NULL,'intf_deal_request_p');


-- ####################################################################--
-- #编译失效的PL/SQP对象#
-- ######################
SET  SERVEROUTPUT  ON

DECLARE  
  CURSOR  cur_invalid_obj  IS  
   SELECT  object_type, object_name   FROM  user_objects  WHERE  status = ' INVALID ' ; 
 rec_invalid_obj cur_invalid_obj % ROWTYPE;
 
 vn_sqlcode  NUMBER ( 10 );
 vs_sqlerrm  VARCHAR ( 200 );
BEGIN  
  OPEN  cur_invalid_obj;
  FETCH  cur_invalid_obj  INTO  rec_invalid_obj;
  WHILE  cur_invalid_obj % FOUND LOOP
  DBMS_OUTPUT.put_line( ' Recompiling  ' || rec_invalid_obj.object_type || ' : ' || rec_invalid_obj. object_name || ' . ' );
  
   -- 1、使用函数
  DBMS_DDL.ALTER_COMPILE(rec_invalid_obj.object_type, NULL ,rec_invalid_obj. object_name );
  
   -- 2、使用SQL语句
   -- ALTER rec_invalid_obj.object_type rec_invalid_obj.object_name COMPILE;
  END  LOOP;
  CLOSE  cur_invalid_obj;
EXCEPTION
  WHEN  OTHERS  THEN
  vn_sqlcode : =  SQLCODE;
  vs_sqlerrm : =  SUBSTR(SQLERRM, 1 , 200 );
  DBMS_OUTPUT.put_line( ' ERROR CODE= ' || vn_sqlcode); 
  DBMS_OUTPUT.put_line( ' ERROR MSG= ' || vs_sqlerrm); 
   IF  cur_invalid_obj % ISOPEN  THEN
    CLOSE  cur_invalid_obj;
   END   IF ; 
END ;