totn Oracle / PLSQL

Oracle / PLSQL: Avoid data not found error in PLSQL code

Question: 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
FROM 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.

Answer: 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;

If the COUNT(1) function returns at least one record, then you can perform the "original select statement".