Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raja.

Asked: July 08, 2021 - 10:57 am UTC

Last updated: July 09, 2021 - 3:55 am UTC

Version: 18.4.0

Viewed 10K+ times! This question is

You Asked

HI Tom,

I have 2 question here. can you please clarify me on these.


Q1.

I know we cant get No-data-found exception in select bulk-collect but what I'm trying to do is, which input data is not found or thrown error while trying to process.
I have a input table which has 50 IDs and only 45 IDs present in the DB and I want to stop the processing for 1st ID not present in the DB or throws error also send back the caller saying this ID is not present.


Q2.

Similar to above one, I'm have input table and I'm trying to process 50 updates using forall and I want to stop the execution when the first error occurs and send back which ID failed.


Can you please suggest me some ideas.

and Chris said...

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 found


So 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 array


2. 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

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Save Exceptions?

Don, July 08, 2021 - 2:45 pm UTC

Would SAVE EXCEPTIONS clause help here?
Connor McDonald
July 09, 2021 - 3:55 am UTC

Yes still :-)

Save Exceptions?

Don, July 08, 2021 - 2:45 pm UTC

Would SAVE EXCEPTIONS clause help here?
Connor McDonald
July 09, 2021 - 3:37 am UTC

Yes,

SAVE EXCEPTIONS is useful for picking an entire set of potential errors, similar to DML error logging

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library