create or replace type obj_table as object (id number,col varchar2(100)); type table_type is table of obj_table; function f_returntable(str in out varchar2) return table_type
as
t_1 table_type;
begin
if str is null then
return t_1;
end if;
while instr(str,',')>0 loop
t_1.extend;
t_1(t_1.count).id:=t_1.count;
t_1(t_1.count).col:=substr(str,1,instr(str,',')-1);
str:=substr(str,instr(str,','),length(str));
end loop;
t_1(t_1.count).id:=t_1.count;
t_1(t_1.count).col:=str;
return t_1;
end f_returntable;这样调用时出错,
select * from table(mypkg.f_returntable('a,b,cc'))
ORA-00902: 无效数据类型请问应该怎样调用返回是表类型的函数啊?
as
t_1 table_type;
begin
if str is null then
return t_1;
end if;
while instr(str,',')>0 loop
t_1.extend;
t_1(t_1.count).id:=t_1.count;
t_1(t_1.count).col:=substr(str,1,instr(str,',')-1);
str:=substr(str,instr(str,','),length(str));
end loop;
t_1(t_1.count).id:=t_1.count;
t_1(t_1.count).col:=str;
return t_1;
end f_returntable;这样调用时出错,
select * from table(mypkg.f_returntable('a,b,cc'))
ORA-00902: 无效数据类型请问应该怎样调用返回是表类型的函数啊?
SQL> create or replace function f_returntable(str1 in varchar2) return table_type
2 as
3 str varchar2(100);
4 t_1 table_type:=table_type();
5 begin
6 t_1:=table_type();
7 str:=','||str1||',';
8 for i in 1..(length(str)-length(replace(str,',',''))) loop
9 t_1.extend;
10 t_1(t_1.count):=obj_table(null,null);
11 t_1(t_1.count).id:=t_1.count;
12 t_1(t_1.count).col:=substr(str,instr(str,',',1,i)+1,instr(str,',',1,i+1)-instr(str,',',1,i)-1);
13 end loop;
14 return t_1;
15 end;
16 /
Function created
SQL>select * from table(f_returntable('a,b,cc'))
2 /
SQL> select * from table(f_returntable('a,b,cc'))
2 /
ID COL
---------- --------------------------------------------------------------------------------
1 a
2 b
3 cc
4
原来是我函数中处理的有问题,改进之后可以了.
但还有个问题就是我把函数放在包体中,执行的话就报"ORA-00902: 无效数据类型"