CODEWITHSUNDEEP
oracle-databaseplsql
Nvr
Nvr

Reputation: 171

Not handle no_data_found

I having emp table, in that there is no records. I used those table in SQL and PL/SQL block

SQL> BEGIN
  2   FOR i IN (SELECT * FROM emp WHERE 1=2)    -- emp table having no data
  3   LOOP
  4    dbms_output.put_line('Done');
  5   END LOOP;
  6  EXCEPTION
  7   WHEN no_data_found THEN
  8    dbms_output.put_line('No such value');
  9  END;
 10  /

PL/SQL procedure successfully completed.   -- it shows completed, instead of handling no_data_found

SQL> SELECT * FROM emp
  2  WHERE 1=2;

no rows selected        -- but here it show no rows selected

In pl/sql block, instead of handling no_data_found it shows pl/sql successfully completed

But in SQL statement it shows no rows selected.

I want to know what is actually happened in plsql and sql statement?

Upvotes: 1

Views: 73

Answers (1)

APC
APC

Reputation: 146219

The code between the LOOP and END LOOP clauses is executed once per row in the cursor. Your cursor returns zero rows so it never gets executed.

The cursor loops gracefully handles the empty result by design, because coming to the end of the result set is expected, even when it is zero rows. So it does not hurls the NO_DATA_FOUND exception.

That's why you see no messages.

On the other hand the SQL statement expects a query to return rows and raises no data found when none are returned. However, you are executing the SQL statement in a client - SQL*Plus in this case - which handles this outcome and displays a message.

If you want your PL/SQL to exhibit the same behaviour, don't use a cursor loop:

declare
    l_emp_rec emp%rowtype;
begin
    select *
    into   l_emp_rec
    from   emp
    where  1 = 2; -- emp table having no data

    dbms_output.put_line('Done');

exception
    when no_data_found then
        dbms_output.put_line('No such value');
end;
/

Upvotes: 5

Related Questions