I'm unclear what you're doing here.
1. If you use bulk collect and the query returns no rows, you just get an empty collection. No exception is raised:
declare
n dbms_sql.number_table;
begin
select 1 bulk collect into n
from dual where 1 = 0;
dbms_output.put_line ( 'Fetched ' || n.count );
end;
/
0
NO_DATA_FOUND is raised when you try and access an array element that doesn't exist. Bulk collect populates a densely packed array with the first index being 1:
declare
n dbms_sql.number_table;
begin
with rws as (
select level * 2 x from dual
connect by level <= 5
)
select x bulk collect into n
from rws
where x between 1 and 6;
/* Loop through the array to find elements */
for i in 1 .. n.count loop
dbms_output.put_line ( 'index = ' || i || ' value = ' || n(i) );
end loop;
/* No element 10 in array => this raises NDF; */
dbms_output.put_line ( n(10) );
end;
/
index = 1 value = 2
index = 2 value = 4
index = 3 value = 6
ORA-01403: no data foundSo if you've bulk collected results and want to find row values missing from the results, you need to
- iterate through the values you searched for
- check if these are in the array you fetched
- raise an error as appropriate
e.g.
declare
type n_arr is table of number;
n n_arr;
begin
with rws as (
select level * 2 x from dual
connect by level <= 5
)
select x bulk collect into n
from rws
where x between 1 and 6;
/* Loop through the array to find elements */
for i in 1 .. 6 loop
if i not member of n then
dbms_output.put_line ( 'value ' || i || ' not in array' );
end if;
end loop;
end;
/
value 1 not in array
value 3 not in array
value 5 not in array2. Forall stops processing as soon as it hits an error. You can find which element triggered it by checking sql%bulk_exceptions in an error handler:
create table t (
c1 int not null,
c2 int not null
);
insert into t
with rws as (
select level x from dual
connect by level <= 3
)
select x, x from rws;
commit;
declare
n dbms_sql.number_table;
begin
n := dbms_sql.number_table ( 1 => 1, 2 => 2, 3 => 3 );
forall i in 1 .. n.count
update t
set c2 = case c1 when 2 then null else c2 end
where c1 = n(i);
exception
when others then
dbms_output.put_line ( 'Updated ' || sql%rowcount );
dbms_output.put_line ( 'Error index = ' || sql%bulk_exceptions(1).error_index );
raise;
end;
/
Updated 1
Error index = 2
ORA-01407: cannot update ("CHRIS"."T"."C2") to NULL