Oracle Exception NO_DATA_FOUND

Problem

We want to select a column value from a table based on a key. When no record is found for the key, a special message will be displayed.

Solution

Taking the following table as an example.

SQL> select * from TBL_0321;

        ID VAL
---------- --------------------------------
         1 ok

We create a text file find_id.sql that contains the following scripts. When an id is found, it will display "value is:...". When an id is not found, use "exception when NO_DATA_FOUND" kicks in and "Id not found" is displayed.

set serveroutput on;
declare
msg varchar2(32);
begin
 select val into msg from TBL_0321 where id=&1;
 dbms_output.put_line('value is:'||msg);
 exception when NO_DATA_FOUND then
   dbms_output.put_line('Id not found'); 
end;
/

The following are the outputs from actual running of the script.

SQL> @find_id 1
old   4:  select val into msg from TBL_0321 where id=&1;
new   4:  select val into msg from TBL_0321 where id=1;
value is:ok

PL/SQL procedure successfully completed.

SQL> @find_id 2
old   4:  select val into msg from TBL_0321 where id=&1;
new   4:  select val into msg from TBL_0321 where id=2;
Id not found

PL/SQL procedure successfully completed.