最近在安装一个软件产品时,发现在混合使用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