Using bulk collect with two rowtypes

Question

I have a cursor query which returns me record of two tables like below.

cursor c1 is
select teb.*,te.*
from  table1 teb, table2 te
where te.col1=teb.col2;

How should I collect them in Collections using bulk collect?

I tried:

type tab_rec is table of table1%rowtype index by pls_integer;
var_rec tab_rec;
type tab_rec1 is table of table2%rowtype index by pls_integer;
var_rec1 tab_rec1;

begin
   open c1;
   fetch c1 bulk collect into var_rec,ver_rec1;
   close c1;

But above doesn't seem to be working.

Hi the above cursor will gives you some set of rows those were the combination of both tables. After that you are fetching rows from cursor and putting into type.But we can insert all rows from a cursor into one type. — Tharunkumar Reddy, Aug 14 '15 at 11:25

user272735 · Answer 1 · 2015-08-14T11:32:52.517

Reading the fine manual: %ROWTYPE Attribute

The %ROWTYPE attribute lets you declare a record variable that represents either a full or partial row of a database table or view. For every column of the full or partial row, the record has a field with the same name and data type. If the structure of the row changes, then the structure of the record changes accordingly.

The %rowtype attribute works also with cursors:

-- c1 is a valid cursor
v_rec c1%rowtype;
fetch c1 bulk collect into v_rec;

See also this example from Oracle documentation.

Using bulk collect with two rowtypes

1 Answers1