RE: PLSQL exception handling

From: Goulet, Richard <>
Date: Tue, 14 Jun 2011 10:51:13 -0400
Message-ID: <>


        You might want to try dbms_utility.format_error_backtrace as follows:

declare a integer;

   a := 'ABC';
exception when others then
dbms_output.put_line(DBMS_UTILITY.format_error_backtrace); end;
ORA-06512: at line 3

PL/SQL procedure successfully completed.

Richard Goulet
Senior Oracle DBA/Na Team Leader

-----Original Message-----

[] On Behalf Of Schauss, Peter (ESS) Sent: Thursday, June 09, 2011 3:13 PM
Subject: PLSQL exception handling

Oracle (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;
rec1.col2 :=rec2.colb;


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


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

Peter Schauss

-- Received on Tue Jun 14 2011 - 09:51:13 CDT

Original text of this message