teapot25
Дата: 05.12.2007 10:35:56
create type lookup_row as object ( idx number, text varchar2(20) );
create type lookups_tab as table of lookup_row;
create or replace function Lookups_Fn return lookups_tab is
v_table lookups_tab;
begin
v_table := lookups_tab ( lookup_row ( 1, 'ONE' ) );
for j in 2..9
loop
v_table.Extend;
if j = 2 then v_table(j) := lookup_row ( 2, 'two' );
elsif j = 3 then v_table(j) := lookup_row ( 3, 'THREE' );
elsif j = 4 then v_table(j) := lookup_row ( 4, 'four' );
elsif j = 5 then v_table(j) := lookup_row ( 5, 'FIVE' );
elsif j = 6 then v_table(j) := lookup_row ( 6, 'six' );
elsif j = 7 then v_table(j) := lookup_row ( 7, 'SEVEN' );
else v_table(j) := lookup_row ( j, 'other' );
end if;
end loop;
return v_table;
end Lookups_Fn;
-- 4
select *
from table(cast(Lookups_Fn() as lookups_tab ) );
Все отлично работает.
Теперь я немного меняю функцию
create or replace function Lookups_Fn return lookups_tab is
v_table lookups_tab;
begin
/*
To extend a nested table, you must use the built-in procedure EXTEND,
but to extend an index-by table, you just specify larger subscripts.
*/
/* v_table := lookups_tab ( lookup_row ( 1, 'ONE' ) );
for j in 2..9
loop
v_table.Extend;
if j = 2 then v_table(j) := lookup_row ( 2, 'two' );
elsif j = 3 then v_table(j) := lookup_row ( 3, 'THREE' );
elsif j = 4 then v_table(j) := lookup_row ( 4, 'four' );
elsif j = 5 then v_table(j) := lookup_row ( 5, 'FIVE' );
elsif j = 6 then v_table(j) := lookup_row ( 6, 'six' );
elsif j = 7 then v_table(j) := lookup_row ( 7, 'SEVEN' );
else v_table(j) := lookup_row ( j, 'other' );
end if;
end loop;
*/
select 1, 'aaaa'
bulk collect into v_table
from dual;
return v_table;
end Lookups_Fn;
При компиляции функции получаю ошибку ORA-00947: not enough values.