| Author |
access inner table in a loop
|
Howard Zhao
Greenhorn
Joined: May 06, 2002
Posts: 27
|
|
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
|
 |
 |
|
|
subject: access inner table in a loop
|
|
|