Why does my exception prevent code from running

Question

I'm a trainee developer who has been put in charge of other people's code so most of my work will be modifying their work. I'm using Report builder on Oracle 10g.

I have the following setup in a formula:

function get_addressFormula return Char is
begin
if :payee_ctc_id is not null then
begin
 select  a.address
        ,a.address2
        ,a.address3
        ,g.location
            ,g.ppostcode
 into    :address1
        ,:address2
        ,:address3
        ,:address4
        ,:postcode

 from ctc_address a
     ,geo_locations g

 where a.addresstypeid = 1  
 and   a.costcentreid = :payee_ctc_id
 and   g.locationid = a.locationid
 and   a.addressid = (select max(i.addressid)   
                      from  ctc_address i
            where i.costcentreid = :payee_ctc_id
            and   i.addresstypeid = 1);

exception
    when others then
      return null;

    while trim(:address1) is null and (trim(:address2) is not null or trim(:address2)  is not null or trim(:address4) is not null)
    loop
      :address1 := :address2; 
      :address2 := :address3; 
      :address3 := :address4; 
      :address4 := '';
    end loop;

    while trim(:address2) is null and (trim(:address3) is not null or trim(:address4) is not null)
    loop
      :address2 := :address3; 
      :address3 := :address4; 
      :address4 := '';
    end loop;

    while trim(:address3) is null and trim(:address4) is not null
    loop
      :address3 := :address4; 
      :address4 := '';
    end loop;

end;
else
 begin
  <else code>
 end;
 end if;

return 'y';
end;

This is the full function except for the last else block. I tried no_data_found but still doesn't work.

@tbone. I'm not sure how to do that. I did some Googling on RAISE with little luck so far.

Answer 1

See Block structure:

<< label >> (optional)
DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions (errors) raised in executable part]
END;

You need a BEGIN/END for each EXCEPTION:

if :payee_id is not null then    
   begin
      <Select statement which place results into placeholders>
   exception
      when NO_DATA_FOUND then
         return null;
   end;
   <code>    
else
   <else code>
end if;

Also note that the use of WHEN OTHERS not followed by a RAISE is a bad code smell. You don't want to ignore ALL errors, so be specific. Usually you only want to catch a NO_DATA_FOUND.