ORACLE编译总结



1.最终脚本实现如下
DECLARE
  v_sqlstr VARCHAR2(1000);
  v_error_code         VARCHAR2(10);                           --错误码 
  v_description        VARCHAR2(160);                          --错误信息 
BEGIN
 
  FOR v_cur IN
    (SELECT *
      FROM user_objects
     WHERE status = 'INVALID'
       AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY','TRIGGER'))
  LOOP
     dbms_ddl.alter_compile(TYPE   => v_cur.object_type,
                            SCHEMA => '',
                            NAME   => v_cur.object_name);
  END LOOP;
 
  FOR v_view_cur IN
    (SELECT 'ALTER VIEW ' || object_name || ' COMPILE'  sqlstr
       FROM user_objects WHERE status = 'INVALID' AND object_type = 'VIEW')
  LOOP
    v_sqlstr := v_view_cur.sqlstr;
    EXECUTE IMMEDIATE v_sqlstr;
  END LOOP;
 
EXCEPTION
  WHEN OTHERS THEN
    v_error_code   := SQLCODE; 
    v_description   := substr(sqlerrm, 1, 160); 
END;

2.注意点

（1）dbms_ddl.alter_compile 支持的 object_type 只有几类，并不包含 view 类型

（2）SELECT 'ALTER VIEW ' || object_name || ' COMPILE'  sqlstr
       FROM user_objects WHERE status = 'INVALID' AND object_type = 'VIEW'

这个语句中 COMPILE 后面不可以加 ; 分号，否则跑的时候，会报错 ORA-00911: 无效字符