首页 » ORACLE » bulk collect 会不会引发not data found?

bulk collect 会不会引发not data found?

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

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Sonja Volper | #1
    2011-12-21 at 09:20

    magnificent submit, very informative. I ponder why the opposite specialists of this sector don’t realize this. You must proceed your writing. I’m confident, you’ve a huge readers’ base already!