Oracle Bug: 在PL/SQL中使用开窗函数后报ORA-00905

今天在一个存储过程中使用到了分析函数中的开窗函数的功能,开窗函数是用来指定分析函数
工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。结果编译时老是报错：
PL/SQL: ORA-00905: missing keyword, 把该SQL语句从程序中抽出来在sqlplus下单独执行
没有问题, 想到好像以前的版本(似乎是8i)中都不支持在PLSQL中写分析函数,怀疑在新版本中支持得有问题,在metalink中
搜了下,果然是Bug 3083373 ：
https://metalink.oracle.com/metalink/plsql/f?p=130:14:11074853651227166603::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,3083373.8,1,0,1,helvetica
下面摘录部分内容(呵呵,方便一些没有metalink帐号的朋友)

[@more@]

This issue is fixed in
9.2.0.7 (Server Patch Set)
10.1.0.5 (Server Patch Set)
10.2.0.1 (Base Release )

PROBLEM:
--------
ORA-905 occur at the time of compile when the SQL statement
using the analysis phrase (analytic_clause) within PL/SQL.
.
====================
SQL> CREATE OR REPLACE PROCEDURE test_proc
2 AS
3 dummy number;
4 BEGIN
5 SELECT MIN(v1)
6 OVER ( ORDER BY v1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW )
7 AS onebefore
8 INTO dummy
9 FROM foo;
10 END test_proc;
11 /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
-------- ------------------------------------------------
5/3 PL/SQL: SQL Statement ignored
6/59 PL/SQL: ORA-00905: missing keyword < ---------------------- *
====================

.
DIAGNOSTIC ANALYSIS:
--------------------
When we execute only select statement, it performs normally.
So, we suspect the problem at the time of compile of PL/SQL.

.
====================
SQL> SELECT MIN(v1)
2 OVER ( ORDER BY v1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW )
3 AS onebefore
4 FROM foo;

.
ONEBEFORE
----------
1

====================
.
And, if using dynamic SQL, ORA-905 will not generate.

.
.

WORKAROUND:
-----------
Using Dynamic SQL.
. ==========
CREATE OR REPLACE PROCEDURE test_proc
AS
dummy number;
sql_text varchar2(2000);
column_value varchar2(32767);
BEGIN
sql_text :='SELECT MIN(v1)
OVER ( ORDER BY v1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW )
AS onebefore
FROM foo';
execute immediate sql_text into column_value;
dbms_output.put_line(column_value);
END test_proc;
/
==========
.
----&gt work well.
.
.
TEST CASE:
----------
Please execute the following script.
.
====================
DROP TABLE FOO;
CREATE TABLE FOO (V1 NUMBER);
INSERT INTO FOO VALUES(1);
COMMIT;
.
CREATE OR REPLACE PROCEDURE test_proc
AS
dummy number;
BEGIN
SELECT MIN(v1)
OVER ( ORDER BY v1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW )
AS onebefore
INTO dummy
FROM foo;
END test_proc;
/
====================

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

转载于:http://blog.itpub.net/3898/viewspace-865997/