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 ...