查看PL/SQL編譯時的錯誤信息

系統 2019-08-12 01:52:11 2646 0

??? 編譯無效對象是DBA與數據庫開發人員常見的工作之一。對于編譯過程中的錯誤該如何去捕獲，下面給出兩種捕獲錯誤的方法。

一、當前數據庫版本信息及無效對象

??? 1、查看當前數據庫版本???

SQL> select * from v$version;                                      

                                                                   

BANNER                                                             

----------------------------------------------------------------   

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi   

PL/SQL Release 10.2.0.4.0 - Production                             

CORE    10.2.0.4.0      Production                                 

TNS for Solaris: Version 10.2.0.4.0 - Production                   

NLSRTL Version 10.2.0.4.0 - Production

??? 2、獲得數據庫中的無效對象

set linesize 180                                                                                        

col object_name format a45                                                                              

SELECT owner, object_name, object_type, status                                                          

FROM dba_objects                                                                                        

WHERE status = 'INVALID'                                                                                

     AND                                                                                                

     object_type IN ('PROCEDURE', 'FUNCTION', 'TRIGGER', 'VIEW', 'PACKAGE');                            

                                                                                                        

OWNER                          OBJECT_NAME                                   OBJECT_TYPE         STATUS 

------------------------------ --------------------------------------------- ------------------- -------

OTC_WRHS_POSITION              OTC_WRHS_POSITION_PCK_tmp                     PACKAGE             INVALID

??? 3、編譯無效對象(編譯方法很多，在此不一一列出)????

/**************************************************/                          

/* Author: Robinson Cheng                         */                          

/* Blog:   http://blog.csdn.net/robinson_0612     */                          

/* MSN:    robinson_0612@hotmail.com              */                          

/* QQ:     645746311                              */                          

/**************************************************/                          

	                                                                              

--注意該包對象中包體的名字含小寫字符，因此編譯時使用雙引號括起來                

SQL> alter package "OTC_WRHS_POSITION"."OTC_WRHS_POSITION_PCK_tmp" compile body;

二、捕獲編譯錯誤
??? 1、使用show errors捕獲錯誤???

SQL> show errors;                                                               

No errors.                                                                      

                                                                                

SQL> show errors package body "OTC_WRHS_POSITION"."OTC_WRHS_POSITION_PCK_tmp";  

No errors.

??? 2、如果使用show errors無法查詢到錯誤，直接查詢視圖dba_errors???

SQL> desc dba_errors;                                                                                                   

Name           Type           Nullable Default Comments                                                                 

-------------- -------------- -------- ------- ---------------------------------------------------------------          

OWNER          VARCHAR2(30)                                                                                             

NAME           VARCHAR2(30)                    Name of the object                                                       

TYPE           VARCHAR2(12)   Y                Type: "TYPE", "TYPE BODY", "VIEW", "PROCEDURE", "FUNCTION",              

"PACKAGE", "PACKAGE BODY", "TRIGGER",                                                                                   

"JAVA SOURCE" or "JAVA CLASS"                                                                                           

SEQUENCE       NUMBER                          Sequence number used for ordering purposes                               

LINE           NUMBER                          Line number at which this error occurs                                   

POSITION       NUMBER                          Position in the line at which this error occurs                          

TEXT           VARCHAR2(4000)                  Text of the error                                                        

ATTRIBUTE      VARCHAR2(9)    Y                                                                                         

MESSAGE_NUMBER NUMBER         Y                                                                                         

                                                                                                                        

SQL> select owner,name,TEXT from dba_errors where owner='OTC_WRHS_POSITION' and name='OTC_WRHS_POSITION_PCK_tmp' and    

  2  sequence=(select max(sequence) from dba_errors where owner='OTC_WRHS_POSITION');                                   

                                                                                                                        

OWNER                NAME                      TEXT                                                                     

-------------------- ------------------------- ------------------------------------------------------------             

OTC_WRHS_POSITION    OTC_WRHS_POSITION_PCK_tmp PLS-00103: Encountered the symbol "ULL" when expecting one o             

                                               f the following:                                                         

                                                                                                                        

                                                  . ( ) , * @ % & = - + < / > at in is mod remainder not re             

                                               m                                                                        

                                                  <an exponent (**)> <> or != or ~= >= <= <> and or like LI             

                                               KE2_                                                                     

                                                  LIKE4_ LIKEC_ between || multiset member SUBMULTISET_                 

                                               The symbol "." was substituted for "ULL" to continue.

???????????????????????????????????????????????????
??????????????????????????????????????????????

查看PL/SQL編譯時的錯誤信息

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

發表我的評論

最新評論總共0條評論