The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Thanks for the question, Sonu.

Asked: April 03, 2019 - 1:36 pm UTC

Last updated: April 04, 2019 - 5:19 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Good Day Folks,

I have one query regarding the BULK COLLECT clause.
Could you please let me know how we can fetch/handle multiple column's data using Bulk Collect clause. Please feel free to alter below code.

-- BULK COLLECT CLAUSE WITH FETCH INTO CLAUSE
DECLARE
    CURSOR my_cur IS 
        SELECT FIRST_NAME FROM EMPLOYEES;
    TYPE nt_emp IS TABLE OF VARCHAR2(30);
    nt_var nt_emp;
BEGIN
   OPEN my_cur;
   LOOP
     FETCH my_cur BULK COLLECT INTO nt_var; 
        FOR indx IN nt_var.FIRST..nt_var.LAST 
        LOOP
            DBMS_OUTPUT.PUT_LINE(indx||' '||nt_var(indx));
        END LOOP;
     EXIT WHEN my_cur%NOTFOUND;
   END LOOP;   
   CLOSE my_cur;
END;
/

and Connor said...

Here's an example

SQL> create table t as
  2  select owner, object_name, object_type
  3  from all_objects
  4  where rownum <= 5;

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    cursor c is select * from t;
  3    type row_list is table of c%rowtype;
  4    r row_list;
  5  begin
  6    open c;
  7    loop
  8      fetch c bulk collect into r limit 100;
  9      for i in 1 .. r.count
 10      loop
 11         dbms_output.put_line(r(i).owner);
 12         dbms_output.put_line(r(i).object_name);
 13         dbms_output.put_line(r(i).object_type);
 14      end loop;
 15      exit when c%notfound;
 16    end loop;
 17    close c;
 18  end;
 19  /
SYS
I_FILE#_BLOCK#
INDEX
SYS
I_OBJ3
INDEX
SYS
I_TS1
INDEX
SYS
I_CON1
INDEX
SYS
IND$
TABLE

PL/SQL procedure successfully completed.

But a nice thing with PL/SQL is that we auto fetch in batches of 100 anyway for simple cursor loops, eg

SQL> begin
  2    for i in ( select * from t )
  3    loop
  4         dbms_output.put_line(i.owner);
  5         dbms_output.put_line(i.object_name);
  6         dbms_output.put_line(i.object_type);
  7    end loop;
  8  end;
  9  /
SYS
I_FILE#_BLOCK#
INDEX
SYS
I_OBJ3
INDEX
SYS
I_TS1
INDEX
SYS
I_CON1
INDEX
SYS
IND$
TABLE

PL/SQL procedure successfully completed.

is getting the same bulk collect benefit with much easier coding

We're not taking comments currently, so please try again later if you want to add a comment.

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

PL/SQL demos

PL/SQL docs

Query regarding BULK COLLECT CLAUSE

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

PL/SQL demos

PL/SQL docs