Re: PLSQL exception handling
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: peter.schauss@xxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 9 Jun 2011 12:21:14 -0700 (PDT)
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@xxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
