| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: format_call_stack
sorry about that, I did this in 7.3. I didn't think to try it in 7.2 or 7.1.
In 7.3, triggers are stored in compiled format. prior to that they were executed as anonymous blocks and compiled at runtime. Apparently, in 7.3, the fact they were compiled when created made them appear as procedures in the call stack (as real named objects). In versions <7.3, they were anonymous blocks.
I ran this in 7.1, 7.2 and 7.3. Sure enough, only 7.3 could figure out 'who' the trigger was. they others saw anonymous blocks.
On 1 Jul 1997 22:14:07 GMT, "Brian Gastineau" <bgastine_at_giveblood.org> wrote:
>I am trying to use the dbms_utility.format_call_stack in a procedure to
>determine the currently firing trigger. It works if it is initiated from
>SQL*Plus or from SQL*Loader. It returns an "anonymous block" instead of
>the trigger name if it is fired from from a PL/SQL package. Any ideas?
>
>The code used follows:
> (all was originally provided by Thomas Kyte in an earlier posting)
>
>***************************************************************************
>create or replace procedure proc3
>as
> l_str varchar2(4096) default dbms_utility.format_call_stack;
> l_n1 number;
> l_n2 number;
>begin
> l_n1 := instr( l_str, chr(10), -2 );
> l_n2 := instr( l_str, chr(10), -1 );
> l_str := substr( l_str, l_n1, l_n2-l_n1-1 );
>
> dbms_output.put_line( l_str );
>end;
>/
>
>drop table foobar;
>create table foobar ( x number );
>
>create or replace trigger foo_trigg
>before insert or update or delete on foobar
>for each row
>begin
> proc3;
>end;
>***************************************************************************
>
>Thanks,
>Brian Gastineau
>bgastine_at_giveblood.org
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |