CODEWITHSUNDEEP
sqloracle-databaseexception
sbrbot
sbrbot

Reputation: 6447

Implicit cursor and NO_DATA_FOUND exception

I have the following PL/SQL code:

BEGIN
  FOR c IN (SELECT ...) LOOP
     <code1>;
  END LOOP;
  <code2>;
EXCEPTION WHEN NO_DATA_FOUND THEN
  NULL;
END;

This code should run code1 multiple times within a loop and upon finishing this loop code2 should be executed. Otherwise if SELECT query does not find data then I expect this should raise an exception and overstep code2, but this is not happening. Why?

Upvotes: 0

Views: 4552

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

No, that's not what is supposed to happen.

If there is no data, then loop runs 0 times - i.e. it skips code1 and executes code2.

You can define explicit cursors and do the checks for data unavailability like this:

DECLARE
  cursor cur is select 1 a from dual where 1 = 1;
  type tab is table of cur%rowtype;
  v tab;
BEGIN
  open cur;
  loop
    fetch cur bulk collect into v;
    if v.count = 0 then
        raise no_data_found;
    end if;
    dbms_output.put_line('Code1');
  end loop;
  close cur;
  dbms_output.put_line('Code2');
EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('Error');
END;
/

You can easily extends this code to do other things such as divide fetch into batches etc.

Upvotes: 1

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

NO_DATA_FOUND is thrown by statements that must return exactly one row but do not find a matching row, e.g.

DECLARE x NUMBER;
BEGIN
  SELECT foo INTO x FROM bar WHERE xyz='abc';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ...
END;

In your case, you could do the following:

DECLARE foundSomething BOOLEAN := FALSE;
BEGIN
  FOR c IN (SELECT ...) LOOP
     foundSomething := TRUE;
     <code1>;
  END LOOP;     
  IF NOT foundSomething THEN
    NULL;  -- handle the situation
  ELSE
    <code2>;
  END IF;
END;

Upvotes: 3

Related Questions