| how to insert data from ref table(collection) into a table in a loop [message #36162] |
Thu, 08 November 2001 06:02  |
Srinivas
Messages: 138
Registered: June 2000
|
Senior Member |
|
|
Hi,
I have created a type record as
type erec is record(
count tab.matchcount%type);
type tabty as table of erec index by binary_integer;
datac:= tabty;
after processing ,i get some output like īthis in the type datac
[[102]] 0
[[1200]] 2 ....
Now,how do i loop and transfer the data from datac into a table having 2 columns(col1,col2).
Any help is appreciated.
Srinivas
----------------------------------------------------------------------
|
|
|
|
| Re: how to insert data from ref table(collection) into a table in a loop [message #36172 is a reply to message #36162] |
Thu, 08 November 2001 08:45  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
does this help??
create table cat_copy as (select * from cat where rownum < 1);
DECLARE
TYPE cat_pltab_type IS TABLE OF cat%ROWTYPE INDEX BY BINARY_INTEGER;
cat_pltab cat_pltab_type;
CURSOR c1 IS SELECT table_name, table_type FROM cat;
i NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO cat_pltab (cat_pltab.COUNT + 1);
EXIT WHEN c1%NOTFOUND;
END LOOP;
FOR i IN 1 .. cat_pltab.COUNT
LOOP
insert into cat_copy(table_name, table_type) values (cat_pltab (i).table_name, cat_pltab (i).table_type);
END LOOP;
END;
/
----------------------------------------------------------------------
|
|
|
|
| Re: how to insert data from ref table(collection) into a table in a loop [message #36191 is a reply to message #36172] |
Fri, 09 November 2001 00:46 |
Srinivas
Messages: 138
Registered: June 2000
|
Senior Member |
|
|
I have created a type record as
type erec is record(
count tab.matchcount%type);
type tabty as table of erec index by binary_integer;
datac:= tabty;
after processing ,i get some output like īthis in the type datac
[[102]] 0
[[1200]] 2 ....
Now,how do i loop and transfer the data from datac into a table having 2 columns(col1,col2).
I want to transfer the data of index into one column(like values 102,1200) and the other actual values tab.matchcount into another column of table.
I am confused as how to read the value of the index and the other column from the ref table .
Any help is appreciated.
----------------------------------------------------------------------
|
|
|
|
| Re: how to insert data from ref table(collection) into a table in a loop [message #36210 is a reply to message #36172] |
Fri, 09 November 2001 13:11 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I don't really understand the problem. When you say INDEX, do you mean an Oracle Table Index - or the index of the pl/sql table?? if you just want to move data from one table to another this just SQL is required.
insert into TAB2 (col1, col2) select col1, col2 from TAB1;
Why do you need the pl/sql table (... index by binaty integer)??
begin
for rec in (select col1, col1, col3 from T) loop
insert into T2 (col1, col2) values(rec.col1, rec.col2);
insert into T3 (col3) values(rec.col3);
end loop;
end;
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
