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

Views: 118

Reply to This

Replies to This Discussion

 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.

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;


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

Nice answer Valentine.. !!

Thanks

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

Reply to Discussion

RSS

© 2017   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service