CODEWITHSUNDEEP
oracle-databaseplsqloracle11gbulk-collect
user2974954
user2974954

Reputation: 169

Using bulk collect with two rowtypes

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.

Upvotes: 0

Views: 1509

Answers (1)

user272735
user272735

Reputation: 10648

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.

Upvotes: 2

Related Questions