ANBOB™

The FETCH statement never raises a NO_DATA_FOUND exception as it is anticipated that it will eventually hit this condition ,Because the processing of the BULK COLLECT INTO clause is similar to a FETCH loop

sys@ORCL> l
  1  declare
  2  type t_tab is table of number index by binary_integer;
  3  v_t t_tab;
  4  begin
  5  select object_id bulk collect into v_t from dba_objects where object_name='xxxx';
  6  dbms_output.put_line(v_t.count);
  7* end;
sys@ORCL> /
0

PL/SQL procedure successfully completed.

sys@ORCL> declare
  2  v_oid int;
  3  begin
  4  select object_id  into v_oid from dba_objects where object_name='xxxx';
  5  dbms_output.put_line(v_oid);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

sys@ORCL> declare
  2  type t_tab is table of number index by binary_integer;
  3  v_t t_tab;
  4  begin
  5  select object_id bulk collect into v_t from dba_objects where object_name='xxxx';
  6  if v_t.count=0 then
  7    raise_application_error(-20000,'NOT_DATA_FOUND');
  8  end if;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-20000: NOT_DATA_FOUND
ORA-06512: at line 7

打赏

微信扫一扫，打赏作者吧～