Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.Received on Thu Sep 03 1998 - 00:00:00 CDT