主题:	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.

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.

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博客

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

Applies to:

Symptoms

Cause

Solution

最新文章