把表函数授权给其它用户时的bug

最近在安装一个软件产品时,发现在混合使用pipelined函数和表函数时,在赋权限给其它用户时,Oracle 9.2.0.1版本有bug. tom kyte在asktom的文章：
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4447489221109
中建议从9201升级到9205、9206以上

metalink上的BUG号为：2204827
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=2204827&p_database_id=BUG
上面说这个bug在版本9202就fixed了

[@more@]

下面语句测试环境Oracle 9.2.0.1 for Windows

SQL> create table tx_vw ( x int );

Table created

SQL> create or replace package My_Types is
2 type lookups_tab is table of tx_vw%rowtype; end My_Types;
3 /

Package created

SQL> create or replace function Lookups_Fn(l_v_str varchar2)
2 return My_Types.lookups_tab
3 pipelined
4 is
5 v_row tx_vw%rowtype;
6 cur sys_refcursor;
7 begin
8 open cur for l_v_str;
9 loop
10 fetch cur into v_row;
11 exit when cur%notfound;
12 pipe row (v_row);
13 end loop;
14 close cur;
15 return;
16 end;
17 /

Function created

SQL> select * from table(Lookups_Fn ('Select user_id from all_users where rownum < 5'));

X
----------
0
5
11
19

SQL> create view v_test as select * from table(Lookups_Fn ('Select user_id from all_users where rownum < 5'));

View created

SQL> select * from v_test;

X
----------
0
5
11
19

SQL> grant select on v_test to wctsupport;

grant select on v_test to wctsupport

ORA-22905: 无法从非嵌套表项访问行

SQL> grant all on lookups_fn to wctsupport;

Grant succeeded

SQL> grant select on v_test to wctsupport;

grant select on v_test to wctsupport

ORA-22905: 无法从非嵌套表项访问行

***************************************************************************

而在9.2.0.6 for windows和10.1.0.3 for linux环境下不报错,下面是测试语句：(两个版本结果一样)

SQL> create table tx_vw ( x int );

Table created

SQL> create or replace package My_Types is
2 type lookups_tab is table of tx_vw%rowtype; end My_Types;
3 /

Package created

SQL> create or replace function Lookups_Fn(l_v_str varchar2)
2 return My_Types.lookups_tab
3 pipelined
4 is
5 v_row tx_vw%rowtype;
6 cur sys_refcursor;
7 begin
8 open cur for l_v_str;
9 loop
10 fetch cur into v_row;
11 exit when cur%notfound;
12 pipe row (v_row);
13 end loop;
14 close cur;
15 return;
16 end;
17 /

Function created

SQL> select * from table(Lookups_Fn ('Select user_id from all_users where rownum < 5'));

X
----------
0
5
11
19

SQL> create view v_test as select * from table(Lookups_Fn ('Select user_id from all_users where rownum < 5'));

View created

SQL> select * from v_test;

X
----------
0
5
11
19

SQL> grant select on v_test to wctsupport;

Grant succeeded

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

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