begin
  begin
    open Report for
      select emplid
        from database[b]1[/b]@somedblink
          where emplid = User;

  exception
    when others
      then
        begin
          open Report for
            select emplid
              from database[b]2[/b]@somedblink
                where emplid = User;
        exception
          when others
            then
              'Both Instances are down.  You are hosed'
        end;

exception
  when others
    then
      null;

end;

exception
  when others
    then
      null;

procedure getResult is

begin
    Response := GlobalVariables.Ok;
       
       begin   
         open var_Results for
           select 1 from dual@db1;
        
       exception
         when others
           then     
            open var_Results for
            select 1 from dual@db2;
        
             exception
               when others
                 then
                  /* this is from IRAPDW */      
                  begin
                    open var_Results for
                      select 1 from dual@db3;
        
                  exception
                    when others
                      then
                        Response := sqlerrm; 
                  end; 
             end; 
    end;
  end;

CREATE OR REPLACE PACKAGE GlobalVariables is


  A               varchar2(1)   default 'A';
  Ok              varchar2(2)   default 'Ok';
  .
  .
  .
  .
  etc
  
end GlobalVariables;

SQL> declare
  2    l_sum number(1);
  3  begin
  4    l_sum := 100 + 1000;
  5  exception
  6    when others then
  7      dbms_output.put_line(sqlerrm);
  8  end;
  9  /
ORA-06502: PL/SQL: numeric or value error: number precision too large

PL/SQL procedure successfully completed.

SQL>

procedure getSSOName (var_emplid  in  varchar2, 
                      var_Results out sys_refcursor, 
                      Response    out varchar2)is


begin
    Response := GlobalVariables.Ok;
    .
    .
    .
    Response := sqlerrm; 

Response := GlobalVariables.Ok;

Response := varchar2(200);

declare
  l_response <what is here?>
begin
  getSSOName(some_emplid, blabla, L_RESPONSE);

SQL> create or replace procedure p_test (par_out out varchar2)
  2  is
  3  begin
  4    par_out := 'ORA-12345 some text here';
  5  end;
  6  /

Procedure created.

SQL> declare
  2    l_out number;      --> wrong! Should be VARCHAR2
  3  begin
  4    p_test (l_out);
  5    dbms_output.put_line(l_out);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4


SQL>

           select some...columns....
             from some table 
               where empid  = 'my employee id'


ORA-01722: invalid number
ORA-02063: preceding line from db3