Pages

Friday 12 July 2013

Do we face no_data_found exception with cursors?

Oracle FAQ

Do we face no_data_found exception with cursors?

  • ·         No, no_data_found exception doesn’t occur with Cursors.
  • ·         We face no_data_found exception with select statements in PLSQL but not in SQL.

This is strange but true .Possible reason for this behavior is PLSQL is blocks structured language and don’t have input output capability of its own. We have to take the outputs in scalar or composite variables. SQL have output capability of its own & directly display the results.

  • ·         For DML Statements no No_data_found exception in PLSQL.
  • ·         No no_data_found exception with BULK Collect statements in PLSQL Select statement.


Examples:


There is no employee_id 0 in the employee table; SQL will give no row selected as output.


SQL> select last_name from employees where employee_id=0;
 
No rows selected

IN PLSQL same statement returns no data found exception

SQL> declare
vlname varchar2(800):=' No_DATA_FOUND EXCEPTION OCCUR WITH SELECT STATEMENT IN        PLSQL;
    begin
    select last_name into vlname from employees where employee_id=0;
    dbms_output.put_line(vlname);
    end;
    /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

Now the cursors, where we don’t face the no_data_found exception.

-- Simple cursor example
SQL>    declare
    cursor c1 is
    select last_name from employees where employee_id=0;
    vlname varchar2(500):='No No_DATA_FOUND EXCEPTION WITH CURSORS';
    begin
    open c1 ;
    loop
    fetch c1 into vlname;
   exit when c1%notfound or c1%notfound is null or c1%rowcount=0;
  end loop;
  close c1;
  dbms_output.put_line(vlname);
 end;
SQL> /
No No_DATA_FOUND EXCEPTION WITH CURSORS                                        

PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------------------------------------
-- Cursor For loop Example
SQL>  declare
   cursor c1 is
    select last_name from employees where employee_id=0;
    vlname varchar2(500):='No No_DATA_FOUND EXCEPTION WITH CURSORS';
    begin
    for i in c1 loop
    dbms_output.put_line(vlname);
    end loop;
  * end;
SQL> /

PL/SQL procedure successfully completed.

-----------------------------------------------------------------------------------------------------
-- cursor for loop with subquery
SQL>
    declare
    vlname varchar2(500):='No No_DATA_FOUND EXCEPTION WITH CURSORS';
    begin
    for i in (select last_name from employees where employee_id=0) loop
    dbms_output.put_line(i.last_name);
    end loop;
  dbms_output.put_line(vlname);
  * end;
    /
No No_DATA_FOUND EXCEPTION WITH CURSORS                                        
PL/SQL procedure successfully completed.

 

No no_data_found exception with Select statement in DML Statements

SQL> create table asp as select * from employees where 1=2;

Table created.
\SQL>
   declare
  vlname varchar2(500):='NO No_DATA_FOUND EXCEPTION WITH DML STATEMENT IN PLSQL';
  begin
  insert into asp (select * from employees where 1=0);
  dbms_output.put_line(vlname);
 end;
  /

No_DATA_FOUND EXCEPTION WITH SELECT STATEMENT IN PLSQL                         
PL/SQL procedure successfully completed.
SQL> select * from asp;
no rows selected


NO NO_DATA_FOUND EXCEPTION WITH BULK COLLECT IN PLSQL SELECT STATEMENT


SQL>
   declare
    type vc_type is table of employees%rowtype
       index by pls_integer;
    ab vc_type;
    begin
    select *  into ab(1) from employees where employee_id=0;
    dbms_output.put_line('with bulk collect no no data found exception');
   end;
SQL> /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6
----------------------------------------------------------------------------------

SQL>
  declare
    type vc_type is table of employees%rowtype
       index by pls_integer;
    ab vc_type;
    begin
    select * bulk collect into ab from employees where employee_id=0;
    dbms_output.put_line('with bulk collect no no data found exception');
  * end;
SQL>
with bulk collect no no data found exception

PL/SQL procedure successfully completed.

  •  Your suggestion , Questions are always welcome ...



8 comments:

  1. If you set an exception block in your error example you will not have this problem.

    ReplyDelete
    Replies
    1. Yes , We can handle this exceptions ... Here i am trying to discuss do we face the No data found exception with cursors & normal select statement.

      Delete
  2. If you look at the documentation (i.e. http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/static.htm#CJAHDJIF ), you will find the following:

    "Before the first fetch, %NOTFOUND returns NULL. If FETCH never executes successfully, the loop is never exited, because the EXIT WHEN statement executes only if its WHEN condition is true. To be safe, you might want to use the following EXIT statement instead:

    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; "

    When you are absolutely sure your cursor will ALWAYS return at least one row, you can get away with coding the PL/SQL as you have done. OTOH, if you are not sure, use the "safe" way to code the EXIT, given above.

    ReplyDelete
  3. at which suitation the first fetch fails will u explain me plzzzzzzzzzzz

    ReplyDelete
  4. the first fetch fail at the line ..
    select last_name into vlname from employees where employee_id=0;
    as there is no data.
    if we have more than one value than it will return too many rows exception.
    we are assigning more than one value to scalar variable.

    ReplyDelete
  5. Please can you let me know how to handle exception if cursor fetches no rows and exit in case if we use cursor for loop

    ReplyDelete
    Replies
    1. -- Cursor For loop Example
      SQL> declare
      cursor c1 is
      select last_name from employees where employee_id=0;
      vlname varchar2(500):='No No_DATA_FOUND EXCEPTION WITH CURSORS';
      begin
      for i in c1 loop
      dbms_output.put_line(vlname);
      end loop;
      * end;
      SQL> /

      Delete