Execute Immediate Bulk Collect in oracle

Execute Immediate Bulk Collect in oracle PL/SQL

You can use the BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store values from each column of a query's result set in a separate collection.

Table data can be filled into indexed tables using bulk collect for row by row processing.  Bulk collect means all rows are fetched in batches.

Using Dynamic SQL also  User can perform  Bulk operations on collections.

Here is the Example.

You have a customer table 

SQL> DESC CUSTOMERS
 Name                                                                                Null?    Type
 ------------------------------------------------------------------------------- -------- --------------
 CUSTOMERID                                                                          NOT NULL NCHAR(5)
 COMPANYNAME                                                                         NOT NULL NVARCHAR2(40)
 CONTACTNAME                                                                                  NVARCHAR2(40)
 CONTACTTITLE                                                                                 NVARCHAR2(30)
 ADDRESS                                                                                      NVARCHAR2(60)
 CITY                                                                                         NVARCHAR2(15)
 REGION                                                                                       NVARCHAR2(15)
 POSTACODE                                                                                    NVARCHAR2(10)
 PHONE                                                                                        NVARCHAR2(24)
 FAX                                                                                          NVARCHAR2(24)

Here is the example of BULK COLLECT using DYNAMIC SQL/EXECUTE IMMEDIATE

--declare a indexed table of customers table

--define type of that table

--declare variable to hold sql statement here select

--execute the dynamic sql

--display bulk collect data using for loop

declare
type typecustomers is table of customers%rowtype;
v_cust typecustomers;
sql_stmt varchar2(250);
begin
sql_stmt :='select * from customers';
execute immediate sql_stmt  bulk collect into v_cust;
if SQL%ROWCOUNT >=0 then
dbms_output.put_line('rows inserted into indexed table');
else
dbms_output.put_line('rows failed to fetch into bulk collect');
end if;
for i in v_cust.first..v_cust.last
loop
dbms_output.put_line(v_cust(i).customerid||' '||v_cust(i).contactname||' '||v_cust(i).REGION);
end loop;
end;

SQL>Set Serveroutput on;

SQL> /
rows inserted into indexed table
HUNGC Yoshi Latimer OR
HUNGO Patricia McKenna Co. Cork
ISLAT Helen Bennett Isle of Wight
KOENE Philip Cramer
LACOR Daniel Tonini
LAMAI Annette Roulet
LAUGB Yoshi Tannamuri BC
LAZYK John Steel WA
LEHMS Renate Messner
LETSS Jaime Yorres CA
LILAS Carlos Gonzßlez Lara

TAGS: Execute Immediate Bulk Collect in oracle,declare indexed table in PL/SQL block, BULK COLLECT INTO using Execute Immediate.