ignore "no data found" error

Hi,

How can I make Oracle 10g to ignore "no data found" error?

I have this peace of code which processes insert statement based on the existance of a record. The logic is right but, the only thing that bothers me is stupid error message, which stops the execution when no data is found in *first select into statement*...   :(

Anyone has ever dealt with this kind of issue?

DECLARE
   zip mp_postal_codes.postal_code_pk%TYPE;
   zip2 mp_city_nicknames.postal_code_pk%TYPE;
   state mp_state_province_codes.state_province_id_pk%TYPE;
   city mp_postal_codes.city%TYPE;
BEGIN
   select case when (trim(cn.postal_code_pk) is null) then null else cn.postal_code_pk end as zp into zip2
         from mp_city_nicknames cn
            inner join mp_state_province_codes spc
               on cn.state_province_id_fk = spc.state_province_id_pk
         where cn.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE;

   select spc.state_province_id_pk, pc.postal_code_pk into state, zip
         from mp_postal_codes pc
            inner join mp_state_province_codes spc
               on pc.state_province_id_fk = spc.state_province_id_pk
         where pc.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE
         and rownum=1;

   select case when (trim(pc.city) is null) then null else pc.city end as ct into city
         from mp_postal_codes pc
            inner join mp_state_province_codes spc
               on pc.state_province_id_fk = spc.state_province_id_pk
         where pc.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE
         and pc.city = :P7_CITY
         and rownum=1;  

   if (zip2 is null) then
      if ((zip is not null) and (state is not null) and (city is null)) then        
           insert into
              mp_postal_codes_bak (postal_code_pk, city, state_province_id_fk)
              values (zip, :P7_CITY, state);
           zip := null;
           state := null;
      end if;
      zip2 := null;
   end if;

   --EXCEPTION
      --when others then
         --zip := null;
END;
LVL 13
davidlars99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

davidlars99Author Commented:
should I be processing "insert statement" based on the exception?

If yes, what is the exception name on "no data found" error???
0
davidlars99Author Commented:
ohh, one more thing!

this is not your regular oracle, this is HTML DB application that I'm trying to fix.

HTML DB sucks big time....!!!
0
paquicubaCommented:
What exactly do you want to do when you catch the exception?

See this example:

 declare
  v_dummy dual.dummy%type;
  begin
    begin
    select dummy into v_dummy from dual where dummy = 'y';    -- If no data is found here then go the exception section
    exception
    when no_data_found then
    select dummy into v_dummy from dual where dummy = 'X';  -- What do you want to do here?
    end;
  dbms_output.put_line( v_dummy );
  end;
  /

X

PL/SQL procedure successfully completed.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

davidlars99Author Commented:
thanks paquicuba, can I do it this way

DECLARE
   zip mp_postal_codes.postal_code_pk%TYPE;
   zip2 mp_city_nicknames.postal_code_pk%TYPE;
   state mp_state_province_codes.state_province_id_pk%TYPE;
   city mp_postal_codes.city%TYPE;
BEGIN
   select case when (trim(cn.postal_code_pk) is null) then null else cn.postal_code_pk end as zp into zip2
         from mp_city_nicknames cn
            inner join mp_state_province_codes spc
               on cn.state_province_id_fk = spc.state_province_id_pk
         where cn.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE;
    EXCEPTION
    when no_data_found then
       zip2 := null;

   select spc.state_province_id_pk, pc.postal_code_pk into state, zip
         from mp_postal_codes pc
            inner join mp_state_province_codes spc
               on pc.state_province_id_fk = spc.state_province_id_pk
         where pc.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE
         and rownum=1;
    EXCEPTION
    when no_data_found then
       state := null;
       zip := null;

   select case when (trim(pc.city) is null) then null else pc.city end as ct into city
         from mp_postal_codes pc
            inner join mp_state_province_codes spc
               on pc.state_province_id_fk = spc.state_province_id_pk
         where pc.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE
         and pc.city = :P7_CITY
         and rownum=1;  
    EXCEPTION
    when no_data_found then
       city := null;

   if (zip2 is null) then
      if ((zip is not null) and (state is not null) and (city is null)) then        
           insert into
              mp_postal_codes_bak (postal_code_pk, city, state_province_id_fk)
              values (zip, :P7_CITY, state);
           zip := null;
           state := null;
      end if;
      zip2 := null;
   end if;

   EXCEPTION
      when others then
         zip := null;
END;
0
davidlars99Author Commented:
I mean to have an exception after each "select into" ???
0
paquicubaCommented:
you'll then need 3 enclosed blocks to handle each exception:

DECLARE
   zip mp_postal_codes.postal_code_pk%TYPE;
   zip2 mp_city_nicknames.postal_code_pk%TYPE;
   state mp_state_province_codes.state_province_id_pk%TYPE;
   city mp_postal_codes.city%TYPE;
BEGIN
   

   BEGIN
   select case when (trim(cn.postal_code_pk) is null) then null else cn.postal_code_pk end as zp into zip2
         from mp_city_nicknames cn
            inner join mp_state_province_codes spc
               on cn.state_province_id_fk = spc.state_province_id_pk
         where cn.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE;
    EXCEPTION
    when no_data_found then
       zip2 := null;
    END;

   
   BEGIN
   select spc.state_province_id_pk, pc.postal_code_pk into state, zip
         from mp_postal_codes pc
            inner join mp_state_province_codes spc
               on pc.state_province_id_fk = spc.state_province_id_pk
         where pc.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE
         and rownum=1;
    EXCEPTION
    when no_data_found then
       state := null;
       zip := null;
    END;

   
   BEGIN
   select case when (trim(pc.city) is null) then null else pc.city end as ct into city
         from mp_postal_codes pc
            inner join mp_state_province_codes spc
               on pc.state_province_id_fk = spc.state_province_id_pk
         where pc.postal_code_pk = :P7_POSTAL_CODE
         and spc.state_province_abbr = :P7_STATE
         and pc.city = :P7_CITY
         and rownum=1;  
    EXCEPTION
    when no_data_found then
       city := null;
    END;

   
if (zip2 is null) then
      if ((zip is not null) and (state is not null) and (city is null)) then        
           insert into
              mp_postal_codes_bak (postal_code_pk, city, state_province_id_fk)
              values (zip, :P7_CITY, state);
           zip := null;
           state := null;
      end if;
      zip2 := null;
   end if;

   EXCEPTION
      when others then
         zip := null;
END;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davidlars99Author Commented:
thanks man! great help!!!
0
paquicubaCommented:
Anytime!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.