Re: How to find out which procedure is currently executing ?

A copy of this was sent to "XXX" <nr_5000_at_yahoo.com> (if that email address didn't require changing) On Thu, 3 Sep 1998 11:01:33 +0400, you wrote:

> In a trigger I would like to know which procedure (if any) is currently
>executing.
>Now I'm using a package. But maybe there's another, _Oracle_ way to find out
>which procedure (if any) is currently running ?
>
>Regards,
>George
>
>

You can get all of this information from the call_stack (dbms_utility.format_call_stack) -- you'll have to parse it out but its there. For example:

SQL> create table t ( x int );
Table created.

SQL> create trigger t_trigg
  2 before insert or update or delete on T   3 begin
  4 dbms_output.put_line( dbms_utility.format_call_stack );   5 end;
  6 /
Trigger created.

SQL> insert into t values ( 1 );
----- PL/SQL Call Stack -----
  object line object
  handle number name
803b5e20 2 TKYTE.T_TRIGG

1 row created.

SQL> begin
  2 insert into t values ( 1 );
  3 end;
  4 /
----- PL/SQL Call Stack -----
  object line object
  handle number name

803b5e20         2  TKYTE.T_TRIGG
80adf95c         2  anonymous block

PL/SQL procedure successfully completed.

SQL> create or replace procedure t_proc
  2 as
  3 begin
  4 insert into t values ( 1 );   5 end;
  6 /
Procedure created.

SQL> execute t_proc
----- PL/SQL Call Stack -----
  object line object
  handle number name

803b5e20         2  TKYTE.T_TRIGG
8056dbf0         4  procedure TKYTE.T_PROC
805b29cc         1  anonymous block

PL/SQL procedure successfully completed.

so, that shows what the call stack would look like from a straight INSERT without pl/sql, from pl/sql but in an anonymous block and finally from a pl/sql procedure itself (a package would be similar)...

hope this helps...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.