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

by Jae Mullikin

Jan 13, 2014

 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

Load Previous Replies
  • up

    Wm Ector

    Nice answer Valentine.. !!

    Feb 21, 2014

  • up

    Kari Mennella

    Thanks

    Feb 24, 2014

  • up

    Imran Amjad

    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

    Nov 6, 2014