10g下编译存储过程导致PLS-00801报错的问题

最新推荐文章于 2021-04-15 23:39:57 发布

cqlilinfeng2009

最新推荐文章于 2021-04-15 23:39:57 发布

阅读量1.5k

点赞数

文章标签：数据库

本文介绍在Oracle 10g Release 2中，原本能在Oracle 9i正常编译的过程化存储过程出现PLS-00801错误的原因及解决方案。该问题源于Oracle对PL/SQL块内的静态SQL进行了规范化处理，导致含有特定空白字符或注释的SQL语句无法正确解析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主题:	Stored Procedure Does Not Compile in 10g release 2 leads to PLS-00801
	文档 ID:	注释:377031.1	类型:	PROBLEM
	上次修订日期:	30-JUL-2007	状态:	PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.
Oracle 10g release 2 - 10.2.0.1.0

Symptoms

A procedure that compiles fine within Oracle 9i release 2 - 9.2.0.6 produces an error when compiled within Oracle 10g release 2 - 10.2.0.1.0.

Error
--------
Errors for PROCEDURE :

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00801: internal error [phdcsql_canonicalize_sql:state]

Cause

Compilation of PL/SQL procedures/blocks can fail when they contain static sql which includes extra white space and/or comments.

The failing behavior. is not a really a bug but a change in behavior. due the fix for BUG 2276769 which can cause SQL monitoring difficulties.

The fix for bug 2276769 purposefully changes behaviour of PLSQL parsing such that static SQL within a PLSQL block is canonicalized to improve cursor sharability. This involves removal of non-hint comments, removal of white space and conversion of all non-literals to upper case.

Whilst the fix in bug 2276769 improves sharability of such SQL some customers use comments in the SQL text itself to help with database monitoring and so the fix can lead to loss of monitoring information from the V$SQL and V$SQLTEXT views.

Bug 5310096 which was opened for this issue was closed as duplicate of Bug 3720104.

Solution

The following is a workaround and not a solution. A PLS-801 from compiling a Stored Procedure is due to an ERROR in the canonical parsing Oracle is doing. Setting the event which is nothing more than a code-path switch and "workaround" to the problem. Oracle should be able to parse any SQL inside PL/SQL with the canonical parser and hence the ORA-801 is indicating a bug with that due to some whitespace characters it chokes on.

<10946> can be set at level 64 to disable canonicalization and hence retain user comments and white space.

e.g., Add event="10946 trace name context forever, level 64" into the the init.ora file used to start the instance.

Bug 3720104 introduced the above event to disable canonicalization of sql. 10946>

In few other cases where the error "PLS-801: internal error [phdcsql_canonicalize_sql:state] " occurs matching Bug 6027121. Development is aware of . Development is aware of Bug 6027121 and they are still working on that. Bug 5765958 behavior. is similar to Bug 6027121. There is a one-off patch 5765958 available on top of Oracle 10g 10.2.0.3 patchset for most platforms. Applying this patch have resolved this error as well.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/228190/viewspace-250392/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/228190/viewspace-250392/