OracleConnections

Oracle Community Network

ORACLE/PLSQL: AVOID "DATA NOT FOUND" ERROR IN PLSQL CODE

I'm a new programmer and am trying to include the following statement in my PLSQL code:

select msa_code, mda_desc, zip_code_nk
 sales.msa
 where zip_code_nk = prod_rec.zip_code_nk;

When there is not a zip_code_nk in the msa table, I'm getting an oracle error saying "Data not found".

How can I code around this? It seem the processor just drops to the exception code and records the record as a failed insert.

http://www.techonthenet.com/oracle/questions/no_data.php

15 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Horace Iliff on January 13, 2014 at 5:07pm

To prevent the PLSQL code from dropping to the exception code when a record is not found, you'll have to perform a count first to determine the number of records that will be returned.

▶ Reply

Permalink Reply by Valentine Dambrosia on January 13, 2014 at 5:09pm

Jae nice Question
use this code, hope it will be helpfull to you Thank you.

select COUNT(1) into v_count
 from sales.msa
 where zip_code_nk = prod_rec.zip_code_nk;
  if v_count > 0 then
    select msa_code, mda_desc, zip_code_nk 
   from sales.msa 
   where zip_code_nk = prod_rec.zip_code_nk; 
end if;

▶ Reply

Permalink Reply by Jere Kleiner on January 13, 2014 at 5:14pm

Jae thank you for posting that question...
and
Valentine nice answer its very helpful for me. Your Query also Solved my Data Not Find problem. :)

▶ Reply

Permalink Reply by Wm Ector on February 21, 2014 at 6:36pm

Nice answer Valentine.. !!

▶ Reply

Permalink Reply by Kari Mennella on February 24, 2014 at 5:52pm

Thanks

▶ Reply

Permalink Reply by Imran Amjad on November 6, 2014 at 3:42pm

Well in my opinion the good idea is to use built-in NO_DATA_FOUND exception for this purpose,
for example;

BEGIN
select msa_code, mda_desc, zip_code_nk
from sales.msa
where zip_code_nk = prod_rec.zip_code_nk;
EXCEPTION when no_data_found then
null; -- Or code whatever you want here.
END;

-- The big advantage is that you are avoiding an extra count SELECT from a database
-- using built-in functionality