posted 22 years ago
Hi
I have a question on using Oracle 9i nest table here.
create or replace type fTable2 as TABLE of varchar2(100);
create table fNT2(
tID varchar(200) primary key,
tmpTable fTable2
) nested table tmpTable store as fnt2_ref;
insert into fNT2 values('myTableId', fTable2());
Another flat table is defined as flatTable(item,varchar(50)), I have (maybe) 20 thousand records in it and want to move them to the inner table of fNT2.
What I did was (inside a store procedure):
OPEN cur for select item from flatTable;
FETCH cur INTO v_Item1;
FETCH cur INTO v_Item2;
if cur%FOUND then
insert into THE(select tmpTable from fNT2 where tID = 'myTableId') values(v_Item1 || v_Item2);
end if;
It looks very simple, but the thing is inside the fetch loop, in order to access the inner table, I have to do this "select tmpTable from fNT2 where tID = 'myTableId'"
again and again.
Does somebody have a good idear on how to access the inner table inside the loop without repeating that selection?
Thanks a lot.
Howard