0

I have declared a custom type

create type finalrecord as object(aaa varchar2(10),bb varchar2(25));
create type mytable is table of finalrecord;

and in my procedure i have written a for loop

create or replace procedure myprocedure(mytab out mytable)
.
.
for cursor1 in (select * from table)
loop
    select * bulk collect into mytab 
    from table2 where table2.issueid = cursor1.id;
end loop;
end;

But i see only one row in mytab. I understand that bulk collect into overwrites the contents of mytab every time data is fetched. How do i avoid this and append data to the end of "mytab"

Kannan Sundararaj
  • 84
  • 3
  • 13

1 Answers1

0

The idea of BULK COLLECT is to fetch many rows at once and not a single row within a loop. So rewrite your PL/SQL to get rid of the loop:

create or replace procedure myprocedure(mytab out mytable)
  .
  .
  select table2.* bulk collect into mytab
  from table2
  join table where table2.issueid = table.id;

end;

It's simpler and more efficient.

Codo
  • 75,595
  • 17
  • 168
  • 206
  • hey..there....actually i fetch values from a view and not a table. so joining two views is not a good idea :-( – Kannan Sundararaj May 04 '14 at 08:42
  • Why is joining two views supposed to be a bad idea? Where have you heard that? – Codo May 04 '14 at 08:44
  • the data is huge in these two views. thats why. If i do a select from these views it takes much time – Kannan Sundararaj May 04 '14 at 08:55
  • Your original implementation of the stored procedure basically implements a query execution plan with a nested loop. Oracle can do that in a single SQL without the additional overhead of switching between SQL and PL/SQL. So if my proposed implementation is slower than yours, Oracle picks the wrong execution plan, which is most likely due to outdated table statistics. With the proper query and up-to-date statistics, it is possible to make my proposed implementation faster than yours. – Codo May 04 '14 at 09:07