查看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;  

1 2 下一頁