oracle plsql forall - ghdrako/doc_snipets GitHub Wiki

Write data back to the database in bulk

There are three ways you can use the forall statement. The easiest way to use it is to specify a range from a dense collection. If your collection is sparse, you can use the indices of clause. And if you want to use the values of your collection as pointers to another collection, you can use the values of clause. For these examples, create a table to hold the drivers who have a fixed driver number.

create table drivers_with_number
as
select drv.*
from f1data.drivers drv
where 1=2
/

Range

When you specify the start and end index of a collection you want to use in your statement, you use the (implicit) range option of the forall statement. This collection has to be a dense collection.

An exception is raised if one element is missing in the range you specify.

ORA-22160: element at index [xxx] does not exist

declare
cursor c_drivers
is
select drv.*
from f1data.drivers drv
where drv.driver_number is not null
order by drv.dob
;
type drivers_tt is table of f1data.drivers%rowtype
index by pls_integer;
l_drivers drivers_tt;
begin
open c_drivers;
fetch c_drivers
bulk collect into l_drivers;
close c_drivers;
if l_drivers.count > 0
then
forall indx in l_drivers.first .. l_drivers.last
insert into drivers_with_number
values l_drivers( indx );
end if;
end;
/

indices of

If you have a sparse collection (i.e., some elements are missing in the range), you can use the indices of option. Unlike the previous example, where you did not select the drivers with no fixed driver_number in the first place, you now select all the drivers from the database into our collection.

declare
cursor c_drivers
is
select drv.*
from f1data.drivers drv;
type drivers_tt is table of f1data.drivers%rowtype
index by pls_integer;
l_drivers drivers_tt;
begin
open c_drivers;
fetch c_drivers
bulk collect into l_drivers;
close c_drivers;
if l_drivers.count > 0
then
for indx in l_drivers.first .. l_drivers.last
loop
if l_drivers( indx ).driver_number is null
then
l_drivers.delete( indx );
end if;
end loop;
end if;
if l_drivers.count > 0
then
forall indx in indices of l_drivers
insert into drivers_with_number values l_drivers( indx );
end if;
end;
/
select dwn.driverid as id
, dwn.driverref as ref
, dwn.driver_number as num
from drivers_with_number dwn
order by dwn.driver_number
/

values of

If you want to use your collection as a set of pointers in another collection, you can use the values of clause. After fetching all the rows from the table into our collection, fill another collection (l_drivers_with_number) with the records that have a driver_number available. The index of this collection is the driver_number. Also, save the driver_number into a different collection (l_driver_numbers). These are our pointers into the l_drivers_ with_number collection.

declare
cursor c_drivers
is
select drv.*
from f1data.drivers drv
;
type drivers_tt is table of f1data.drivers%rowtype
index by pls_integer;
type driver_numbers_tt is table of pls_integer
index by pls_integer;
l_drivers drivers_tt;
l_drivers_with_number drivers_tt;
l_driver_numbers driver_numbers_tt;
begin
open c_drivers;
fetch c_drivers
bulk collect into l_drivers;
close c_drivers;
if l_drivers.count > 0
then
for indx in l_drivers.first .. l_drivers.last
loop
if l_drivers( indx ).driver_number is not null
then
l_drivers_with_number(l_drivers( indx ).driver_number) :=
l_drivers( indx );
l_driver_numbers( l_driver_numbers.count + 1 ) :=
l_drivers( indx ).driver_number;
end if;
end loop;
end if;
if l_driver_numbers.count > 0
then
forall indx in values of l_driver_numbers
insert into drivers_with_number
values l_drivers_with_number( indx );
end if;
end;
/
select dwn.driverid as id
, dwn.driverref as ref
, dwn.driver_number as num
from drivers_with_number dwn
order by dwn.driver_number
/

Save Exception

the errors are not logged into a table but into a collection in memory. Oracle Database raises a special exception that you can handle. failure_in_forall exception; pragma exception_init( failure_in_forall, -24381 ); All the exceptions are saved into a pseudo-collection: sql%bulk_exceptions. The following are the attributes available in this collection.

• error_code: Holds the corresponding error code
• error_index: Holds the iteration number of the current forall statement
• count: Holds the total number of exceptions

Using forall with save exceptions

declare
cursor c_drivers is
select drv.*
from f1data.drivers drv
where drv.driver_number is not null
order by drv.dob;
type drivers_tt is table of f1data.drivers%rowtype
index by pls_integer;
l_drivers drivers_tt;
failure_in_forall exception;
pragma exception_init( failure_in_forall, -24381 );
begin
open c_drivers;
fetch c_drivers
bulk collect into l_drivers;
if l_drivers.count > 0
then
begin
forall indx in l_drivers.first .. l_drivers.last
save exceptions
insert
into drivers_with_number values l_drivers(indx);
exception
when failure_in_forall then
for indx in 1 .. sql%bulk_exceptions.count
loop
dbms_output.put_line( 'Error '
|| indx
|| ' occurred on index '
|| sql%bulk_exceptions( indx ).
error_index
|| '.'
);
dbms_output.put_line( 'Oracle error is '
|| sqlerrm( -1 *
sql%bulk_exceptions( indx ).
error_code)
); end loop;
end;
end if;
end;
/

close c_drivers;