10g下编译存储过程导致PLS-00801报错的问题
上一篇 /
下一篇 2008-04-24 11:35:10
/ 个人分类：oracle
| 主题: | 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.0Symptoms
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 <procedure name>:
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.
<event: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.
In few other cases where the error "PLS-801: internal error [phdcsql_canonicalize_sql:state] " occurs matchingBug 6027121. Development is aware of. Development is aware ofBug 6027121and they are still working on that.Bug 5765958behavior. is similar toBug 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.
导入论坛
引用链接
收藏
分享给好友
推荐到圈子
管理
举报
TAG:
