OracleConnections

Oracle Community Network

Error in Code

I'm 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.

2 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Eleanor Dee on April 29, 2013 at 11:17am

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.

For example:

-- Check to make sure that at least one record is returned
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;