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

最近在安装一个软件产品时,发现在混合使用pipelined函数和表函数时,在赋权限给其它用户时,Oracle 9.2.0.1版本有bug. tom kyte在asktom的文章：

中建议从9201升级到9205、9206以上

[@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