Wednesday, 28 August 2019

GET EXACT ERROR LINE NUMBER IN PL/SQL USING FORMAT_ERROR_BACKTRACE


DBMS_UTILITY package provides FORMAT_ERROR_BACKTRACE procedure which returns exact line no of error source along with the error source name.
Here in below example, predefined NO_DATA_FOUND exception has been explicitly raised in procedure demo1.
Demo1 procedure is called inside demo2 procedure and demo2 procedure is finally called inside 3rd procedure demo3.in demo3 procedure FORMAT_ERROR_BACKTRACE has been used. On executing demo3 procedure exception raised at line no 5 of demo1 , which was called at line no 3 of demo2 & demo2 was called at line no 4 of demo3.

So in below example FORMAT_ERROR_BACKTRACE shows the error in order of its calling & exact line no(s) are returned with error code.

create or replace procedure demo1(var1 number ,var2 NUMBER,sumVAR OUT number) as
errmsg VARCHAR2(200);
begin
dbms_output.put_line('INSIDE DEMO1');
RAISE NO_DATA_FOUND;
end;


create or replace procedure demo2(a number ,b varchar2, sum_ab out number, multiply_ab out number,err out varchar2) as
begin
dbms_output.put_line('INSIDE DEMO2');
demo1(a ,b,sum_ab);
end;
/


create or replace procedure demo2(a number ,b varchar2, sum_ab out number, multiply_ab out number,err out varchar2) as
begin
dbms_output.put_line('INSIDE DEMO2');
multiply_ab :=A*B;
---calling proc demo1 for calcaulating sum---
demo1(a ,b,sum_ab);
end
;
/
create or replace procedure demo3(a number ,b varchar2) as
begin
dbms_output.put_line('INSIDE DEMO3');
---calling proc demo2 --
demo2(a ,b);
EXCEPTION
WHEN OTHERS THEN NULL
DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
end;


CALLING OF DEMO3 PROCEDURE:
SET SERVEROUTPUT ON;
DECLARE
  VAR1 NUMBER;
  VAR2 VARCHAR2(200);
BEGIN
  VAR1 := 5;
  VAR2 := 7;
  DEMO3(
    VAR1 => VAR1,
    VAR2 => VAR2
  );
END;

/
OUTPUT:

anonymous block completed
INSIDE DEMO3
INSIDE DEMO2
INSIDE DEMO1
ORA-06512: at "TR_LEA_CR.DEMO1", line 5
ORA-06512: at "TR_LEA_CR.DEMO2", line 3
ORA-06512: at "TR_LEA_CR.DEMO3", line 4



This output demonstrates how exact line no of error source can be returned using FORMAT_ERROR_BACKTRACE. This is very useful functionality provided by oracle.

No comments:

Post a comment