Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Orozco.

Asked: May 04, 2016 - 9:50 pm UTC

Last updated: May 05, 2016 - 6:24 am UTC

Version: Database 10g

Viewed 1000+ times

You Asked

Hello Tom. Your instructions and recommendations for proper development are very valuable to me.
I have a concern. What instruction / sentence can I know the name of a package, procedure or function, at runtime, which updates a value in a table?
The situation is this: I do maintenance and many database objects that update a status value in a given table. I created a trigger to find out what new value reaches the state, but can not identify what the object (package, procedure, function, trigger) that it is updating. Thank you very much for your instructions and cooperation. Regards,

and Connor said...

We can use the call stack to extract what we need. Here's an example which shows the entire call stack (which comes out as a string), and how we can parse it to find out what we need

SQL> create or replace
  2  PROCEDURE my_proc is
  3
  4     l_call_stack      VARCHAR2(4001);
  5  BEGIN
  6
  7    l_call_stack := dbms_utility.format_call_stack;
  8    dbms_output.put_line('========================');
  9    dbms_output.put_line(l_call_stack);
 10    dbms_output.put_line('========================');
 11
 12    l_call_stack := substr(l_call_stack,instr(l_call_stack,chr(10),1,3));
 13    l_call_stack := substr(l_call_stack,
 14                               greatest(
 15                                  instr(l_call_stack,'function'),
 16                                  instr(l_call_stack,'package'),
 17                                  instr(l_call_stack,'procedure')
 18                                  ));
 19    l_call_stack := substr(l_call_stack,1,instr(l_call_stack,chr(10))-1);
 20
 21    dbms_output.put_line(l_call_stack);
 22
 23  END;
 24  /

Procedure created.

SQL> sho err
No errors.
SQL>
SQL> set serverout on
SQL> exec my_proc
========================
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FFA35AA050         6  procedure MCDONAC.MY_PROC
000007FFAAE18880         1  anonymous block

========================
procedure MCDONAC.MY_PROC

PL/SQL procedure successfully completed.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

RE

George Joseph, May 05, 2016 - 6:04 am UTC

You could make use of the owa_util.who_called_me to figure out who was the caller of the INSERT/UPDATE/DELETE etc.


create table t2(x int);

create or replace trigger trgg_t2
before insert or update or delete 
on t2
declare
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number;
l_caller_t varchar2(30);
begin

owa_util.who_called_me(l_owner,
                       l_name,
                       l_lineno,
                       l_caller_t
                       );
                              
dbms_output.put_line(l_owner||':'||l_name||':'||l_lineno||':'||l_caller_t);

end;

begin
       insert into t2 values(1);
end;
--::2:ANONYMOUS BLOCK

create or replace procedure p1
as
begin
       insert into t2 values(2);
end;

begin
       p1();
end;

MR:P1:4:PROCEDURE

Connor McDonald
May 05, 2016 - 6:24 am UTC

Thanks for the input George. Good to meet you in person in India !

RE

George Joseph, May 05, 2016 - 8:41 am UTC

It was good to meet you in person too,Connor. Thanks for taking you time and coming and taking awesome sessions in Oracle

Orozco Daniel Fernando, May 05, 2016 - 9:32 pm UTC

Thanks a lot!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library