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 ...
If you set an exception block in your error example you will not have this problem.
ReplyDeleteYes , We can handle this exceptions ... Here i am trying to discuss do we face the No data found exception with cursors & normal select statement.
DeleteIf 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:
ReplyDelete"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.
at which suitation the first fetch fails will u explain me plzzzzzzzzzzz
ReplyDeletethe first fetch fail at the line ..
ReplyDeleteselect 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.
thnx for ans
ReplyDeletePlease 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-- Cursor For loop Example
DeleteSQL> 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> /