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