1

No Data ? No Problem ! Fun and Games with the NO_DATA_FOUND exception | The Anti...

 3 years ago
source link: https://mikesmithers.wordpress.com/2020/05/06/no-data-no-problem-fun-and-games-with-the-no_data_found-exception/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

I can’t remember how long we’ve been in lockdown now but between you and me, I think my Oracle Database may be cracking up.
As Exhibit A, I present the fact that RDBMS seems to raise the NO_DATA_FOUND exception only when it feels like it…

Rotate the Pod please, HAL

NO_DATA_FOUND is one of Oracle’s built-in exceptions and is raised when your code encounters an ORA-01403 : no data found error.
It’s often associated with a select into clause, as this construct will (should) return a single row.
If it doesn’t it should cause either a TOO_MANY_ROWS or a NO_DATA_FOUND exception, depending on whether it’s found multiple rows, or couldn’t find any…

set serverout on size unlimited
clear screen
declare
mystring varchar2(100);
begin
select 'I am completely operational, and all my circuits are functioning perfectly'
into mystring
from dual
where 1 = 2;
end;
/

Run this and you will get the aforementioned error :

ORA-01403: no data found

Open the Pod Bay doors, HAL

Let’s go right ahead and create a function with this code :

create or replace function stir_crazy return varchar2 is
mystring varchar2(100);
begin
select 'I am completely operational, and all my circuits are functioning perfectly'
into mystring
from dual
where 1 = 2;
return mystring;
end stir_crazy;
/

We’ve not added any exception handling in the function so the error should propogate back to the caller…

select nvl(stir_crazy, q'[I'm afraid I can't do that, Dave]') as stir_crazy from dual;
STIR_CRAZY                             
----------------------------------------
I'm afraid I can't do that, Dave

That’s right. Rather than raising an ORA-01403 error, Oracle has simply returned NULL.
Have I just made some silly mistake in the code ? Let’s check. This time, we’ll handle the exception, if it’s raised…

begin
if stir_crazy = null then
dbms_output.put_line('Daisy, Daisy, give me your answer, do !');
else
dbms_output.put_line('Just what do you think are you doing, Dave ?');
end if;
exception when no_data_found then
dbms_output.put_line(q'[I've still got the greatest enthusiasm and confidence in the mission]');
end;
/

Run this and we can see that the exception is now raised…

I've still got the greatest enthusiasm and confidence in the mission
PL/SQL procedure successfully completed.

I’ve just picked up a fault in the AE-35 unit

It turns out that Oracle has always behaved in this way.

According to this explanation, NO_DATA_FOUND is not an error when encountered from SQL, merely an “exceptional condition”.

I honestly think you ought to sit down calmly, take a stress pill, and think things over

This may be no more than an interesting quirk, depending on what is considered as SQL in this context…

As an example, let’s look at the HR.COUNTRIES table :

select country_id, country_name, region_id
from countries;
COUNTRY_ID      COUNTRY_NAME                         REGION_ID
--------------- ------------------------------ ---------------
AR              Argentina                                    2
AU              Australia                                    3
BE              Belgium                                      1
BR              Brazil                                       2
CA              Canada                                       2
CH              Switzerland                                  1
CN              China                                        3
DE              Germany                                      1
DK              Denmark                                      1
EG              Egypt                                        4
FR              France                                       1
IL              Israel                                       4
IN              India                                        3
IT              Italy                                        1
JP              Japan                                        3
KW              Kuwait                                       4
ML              Malaysia                                     3
MX              Mexico                                       2
NG              Nigeria                                      4
NL              Netherlands                                  1
SG              Singapore                                    3
UK              United Kingdom                               1
US              United States of America                     2
ZM              Zambia                                       4
ZW              Zimbabwe                                     4
25 rows selected.

The REGION_ID references the REGIONS table :

select region_id, region_name
from regions;
REGION_ID REGION_NAME                  
---------- ------------------------------
1 Europe                       
2 Americas                     
3 Asia                         
4 Middle East and Africa       

…and we have a lookup function to return the Region Name name for each id value…

create or replace function get_region_name( i_region_id in regions.region_id%type)
return regions.region_name%type
is
rtn regions.region_name%type;
begin
select region_name into rtn
from regions
where region_id = i_region_id;
return rtn;
end;
/

Now let’s add in a record that includes a region_id value that will not be found by the function
(yes, I have disabled the Foreign Key that would normally prevent this)…

insert into countries( country_id, country_name, region_id)
values( 'NZ', 'New Zealand', 5);

As we’ve already observed, calling the function from SQL will simply cause a null value to be returned…

select country_name, get_region_name( region_id) as region_id
from countries
where country_id = 'NZ';
COUNTRY_NAME                   REGION_ID     
------------------------------ ---------------
New Zealand                                  

If we now call the function from inside a PL/SQL block, we might expect NO_DATA_FOUND to make an appearance…

set serverout on size unlimited
begin
for r_countries in (
select country_name, get_region_name( region_id) as region_id
from countries)
loop
dbms_output.put_line( r_countries.country_name||' Region : '||r_countries.region_id);
end loop;
end;
/

or possibly not…

Argentina Region : Americas
Australia Region : Asia
Belgium Region : Europe
Brazil Region : Americas
Canada Region : Americas
Switzerland Region : Europe
China Region : Asia
Germany Region : Europe
Denmark Region : Europe
Egypt Region : Middle East and Africa
France Region : Europe
Israel Region : Middle East and Africa
India Region : Asia
Italy Region : Europe
Japan Region : Asia
Kuwait Region : Middle East and Africa
Malaysia Region : Asia
Mexico Region : Americas
Nigeria Region : Middle East and Africa
Netherlands Region : Europe
Singapore Region : Asia
United Kingdom Region : Europe
United States of America Region : Americas
Zambia Region : Middle East and Africa
Zimbabwe Region : Middle East and Africa
New Zealand Region :
PL/SQL procedure successfully completed.

However, if we call the function from a PL/SQL statement then we get a different result :

set serverout on size unlimited
begin
for r_countries in (
select country_name, region_id
from countries)
loop
dbms_output.put_line( r_countries.country_name||' Region :'||get_region_name( r_countries.region_id));
end loop;
end;
/
ORA-01403: no data found
ORA-06512: at "MIKE.GET_REGION_NAME", line 6

Looks like I’m the one cracking up.
The moral of the story ? Don’t rely on NO_DATA_FOUND being raised unless you are executing a PL/SQL statement.

Advertisements
Report this ad

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK