Replace iteration's fetch to BULK COLLECT

Question

There is an object type and table consists of that objects. So here we go:

create or replace type lpu.someobj_o as object
(      
  name VARCHAR2(75),     
  enroll_date DATE,
  id NUMBER(12)
)

CREATE OR REPLACE TYPE lpu."SOMEOBJ_T" IS TABLE OF someobj_o;

There's also PL/SQL function that works in common ETL principle. Here is piece of code function:

  for some_cursor_rec in some_cursor(startTime, recordInterval) loop
  open some_cur2(some_cursor_rec.name, some_cursor_rec.id);
  fetch some_cur2 into some_cursor_rec2;
  if some_cur2%rowcount > 0 then
    loop
      pipe row (
        lpu.someobj_o(
        id => some_cursor_rec2.id, 
        name => some_cursor_rec2.name, 
        enroll_date => some_cursor_rec2.enroll_date
      )
      );
      fetch some_cur2 into some_cursor_rec2;
      exit when some_cur2%notfound;
    end loop;      
  end if;
  close some_cur2;
end loop;

Ok, so the issue is small performance. How can I increase speed of completing of this function? I have read that BULK COLLECT should improve performance. But how can I use it in my case? I have tried BULK COLLECT but it gave me error that type of collection is wrong. Thanks in advance! Really hope for your help!

Answer 1

If performance is your concern, then you should start looking at combining your two cursors.

Current you are executing the query in the some_cursor once, and you are executing the query in cursor some_cur2 as many times as there are rows selected in the first query. And that's highly likely your performance bottleneck.

If you combine the two queries to one query and do a cursor for loop (which executes the query only once), then you'll automatically be bulk fetching 100 rows at a time, so there probably won't be a real need to manually convert it to do bulk fetching.

Regards,
Rob.

Answer 2

Here is a basic example how to use bulk collect into with an object type.

create or replace type someobj_o as object
(      
  name varchar2(75),     
  enroll_date date,
  id number(12)
);
/

create or replace type someobj_t is table of someobj_o;
/

create table someobj_table of someobj_o;

insert into someobj_table values(someobj_o('Joe', current_date, 100));
insert into someobj_table values(someobj_o('Jack', current_date, 101));
insert into someobj_table values(someobj_o('John', current_date, 102));

declare
  v_objs someobj_t;
begin
  select someobj_o(name, enroll_date, id)
    bulk collect into v_objs
    from someobj_table;

  dbms_output.put_line('number of objects in a collection = ' || v_objs.count);
end;
/

---

Update

The same than above with a cursor:

declare
  type objcur_t is ref cursor;
  v_objcur objcur_t;
  v_objs someobj_t;
begin
  open v_objcur for
    select someobj_o(name, enroll_date, id)
    from someobj_table;

  fetch v_objcur bulk collect into v_objs;

  close v_objcur;

  dbms_output.put_line('number of objects in a collection = ' || v_objs.count);
end;
/