查看PL/SQL编译时的错误信息

查看PL/SQL编译时的错误信息

    编译无效对象是DBA与数据库开发人员常见的工作之一。对于编译过程中的错误该如何去捕获，下面给出两种捕获错误的方法。

一、当前数据库版本信息及无效对象

    1、查看当前数据库版本

1. SQL> select * from v$version;                                        
2.                                                                      
3. BANNER                                                               
4. ----------------------------------------------------------------      
5. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi     
6. PL/SQL Release 10.2.0.4.0 - Production                               
7. CORE    10.2.0.4.0      Production                                   
8. TNS for Solaris: Version 10.2.0.4.0 - Production                     
9. NLSRTL Version 10.2.0.4.0 - Production                               

    2、获得数据库中的无效对象

1. set linesize 180                                                                                          
2. col object_name format a45                                                                                
3. SELECT owner, object_name, object_type, status                                                            
4. FROM dba_objects                                                                                          
5. WHERE status = 'INVALID'                                                                                  
6.      AND                                                                                                  
7.      object_type IN ('PROCEDURE', 'FUNCTION', 'TRIGGER', 'VIEW', 'PACKAGE');                              
8.                                                                                                           
9. OWNER                          OBJECT_NAME                                   OBJECT_TYPE         STATUS   
10. ------------------------------ --------------------------------------------- ------------------- -------   
11. OTC_WRHS_POSITION              OTC_WRHS_POSITION_PCK_tmp                     PACKAGE             INVALID  

    3、编译无效对象(编译方法很多，在此不一一列出)                                                                                     

1. --注意该包对象中包体的名字含小写字符，因此编译时使用双引号括起来                   
2. SQL> alter package "OTC_WRHS_POSITION"."OTC_WRHS_POSITION_PCK_tmp" compile body;