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?
 
paquicubaConnect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
davidlars99Author Commented:
thanks man! great help!!!
0
 
paquicubaCommented:
Anytime!
0
All Courses

From novice to tech pro — start learning today.