CODEWITHSUNDEEP
oracle-databaseexceptionplsql
mjsr
mjsr

Reputation: 7590

How can I get the procedure/function name where an exception occurs, in PLSQL?

How can I get a procedure/function name where an exception occurs in PLSQL? I need this to create a log of the procedures that have problems in a package.

Upvotes: 3

Views: 1452

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27261

Unfortunately there is no way to get a stored procedure name, that is wrapped in a package, at run time. Any method, whether it $$PLSQL_UNIT inquiry directive or FORMAT_ERROR_BACKTRACE function of dbms_utility package, that allows you to do that in a case of a stand-alone stored procedure will always give you the anonymous block instead of a name in a case of a stored procedure that is wrapped in the package. Hence your only choice is to capture a package name and the line number on which an error has occurred by using dbms_utility.format_error_backtrace for example.

SQL> create or replace package some_pkg
  2  as
  3    procedure some_proc;
  4  end;
  5  /

Package created

SQL> 
SQL> create or replace package body some_pkg
  2  as
  3     procedure Some_Proc
  4     is
  5       l_var number;
  6     begin
  7       l_var := 1/0;
  8     exception
  9       when others
 10       then dbms_output.put_line(dbms_utility.format_error_backtrace);
 11     end;
 12  end;
 13  /

Package body created

SQL> exec some_pkg.some_proc;

ORA-06512: at "HR.SOME_PKG", line 7

Upvotes: 6

planben
planben

Reputation: 700

Did u mean get all procedures that has 'exception' in their source ? if so then :

select distinct t.name from user_source t
where upper(t.text) like '%EXCEPTION%' or
  upper(t.text) like '%RAISE_APPLICATION_ERROR%';

I hope it helps you :)

Upvotes: 0

Related Questions