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

女爷i 2022-05-15 11:28 497阅读 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.