Re: PLSQL exception handling

You might think about using the format_error_backtrace and format_error_stack 
functions from dbms_utility:

  exception
         --
         -- Let's handle the exception here
         --
         when others then
          dbms_output.put_line('Displaying the error stack:');
          dbms_output.put(dbms_utility.format_error_stack);
          dbms_output.put_line(dbms_utility.format_error_backtrace);

Here's an example:

SQL> create or replace procedure myproc   2  is   3  begin   4        --   5        
-- Informative text to the end user   6        --   7        
dbms_output.put_line('Happily executing myproc');   8        --   9        -- 
But let's raise an error anyway  10        --  11        raise no_data_found;  
12  end;  13  /
  Procedure created.
  SQL> SQL> create or replace procedure yourproc   2  is   3  begin   4        
--   5        -- Yet more informative text   6        --   7        
dbms_output.put_line('Calling myproc');   8        --   9        -- A guaranteed 
error occurs here  10        --  11        myproc;  12  end;  13  /
  Procedure created.
  SQL> SQL> create or replace procedure allproc   2  is   3  begin   4        --   
5        -- And another helpful message   6        --   7        
dbms_output.put_line('Calling yourproc');   8        --   9        -- Call the 
proc which calls the proc  10        -- that generates the guaranteed  11        
-- error  12        --  13        yourproc;  14  exception  15        --  16        
-- Let's handle the exception here  17        --  18        when others then  19         
dbms_output.put_line('Displaying the error stack:');  20         
dbms_output.put(dbms_utility.format_error_stack);  21         
dbms_output.put_line(dbms_utility.format_error_backtrace);  22  end;  23  /
  Procedure created.
  SQL> SQL> -- SQL> -- Let's get this ball rolling SQL> -- SQL> -- The error 
text should point back to SQL> -- the source of the error (line 11 of myproc) 
SQL> -- SQL> SQL> exec allproc Calling yourproc Calling myproc Happily executing 
myproc Displaying the error stack: ORA-01403: no data found ORA-06512: at 
"BING.MYPROC", line 11 ORA-06512: at "BING.YOURPROC", line 11 ORA-06512: at 
"BING.ALLPROC", line 13
  PL/SQL procedure successfully completed.
  SQL> 
David Fitzjarrell





________________________________
From: "Schauss, Peter (ESS)" <peter.schauss_at_ngc.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Sent: Thu, June 9, 2011 12:12:35 PM
Subject: PLSQL exception handling

Oracle 11.2.0.1.0 (Linux x86-64).

I am writing stored procedure which copies information from one table to 
another, doing some transformations in the process.  I am using the %rowtype 
construct for the fetches and inserts and doing a large number of assignment 
statements in the form:

rec1.col1 := rec2.cola;

Since there is a possibility of type conversion errors in some cases, I need to 
be able to trap errors and identify the offending column in the input table.  
The Oracle documentation suggests something like this:

step_num:= <n>
rec1.col1 := rec2.cola;
step_num:=<n+1>
rec1.col2 :=rec2.colb;

exception
    when <error type> dbms_output.put_line('error at '||step_num);
    raise;
end;

Is there a better way for me to identify the location of the error?

Thanks,
Peter Schauss
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l