查看PL/SQL编译时的错误信息 - 军军小站|张军博客

编译无效对象是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元

自定义