Skip to Main Content
  • Questions
  • Is there a self-reference to my PL/SQL program's name?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Raghu.

Asked: February 28, 2001 - 1:53 pm UTC

Last updated: April 21, 2017 - 1:26 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Is there a PL/SQL variable equivalent of the $0 variable in Unix. Within a procedure/function if I want to know the name of the program that I am running, can I get it from a system variable. I plan to write into a log table and I want to pass the program name and comments as parameters. Now, I need to hardcode the program name - is there a way to pick this off an environment variable?
Thanks,
Raghu

and we said...

See
</code> http://asktom.oracle.com/~tkyte/who_called_me/index.html <code>

there i have a procedure "who_called_me" and a function "who_am_i" that returns what you want.

Rating

  (33 ratings)

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

Comments

broken link

Paul Druker, November 30, 2001 - 4:19 pm UTC

Tom, link on this page is broken, it's pointing to </code> http://asktom.oracle.com/~tkyte/who_ <font%20color=
instead of 
http://asktom.oracle.com/~tkyte/who_called_me/index.html <code>


about remote calling

James Su, June 02, 2004 - 6:04 pm UTC

hi Tom,

I found the dbms_utility.format_call_stack is null when called from another database through a db link. What can I do? Thank you.

Tom Kyte
June 02, 2004 - 7:23 pm UTC

you'd have to call the REMOTE package to get the REMOTE call stack..

Call stack - such a wonderful facility...

Connor, June 02, 2004 - 8:47 pm UTC

...now if only the call stack within the *Forms* plsql engine was exposed

Sigh...

Connor

A reader, June 03, 2004 - 3:10 am UTC


still confused

James Su, June 03, 2004 - 11:47 am UTC

hi tom,

Do you mean:
inside function who_called_me, I should call
dbms_utility.format_call_stack@somedblink
to get the REMOTE call stack?

But how do I know where the caller is from?
Can you show me an example? Thank you.



Tom Kyte
June 03, 2004 - 2:00 pm UTC

now that I think about it -- you won't be able to get it at all. the callback would be yet another connection.

sorry -- guess with the dblink it just breaks the chain.

another question about who_called_me

James Su, June 24, 2004 - 5:01 pm UTC

hi tom,
If I try to get the caller's name, and it's a procedure inside a package, what I get is the package name. The procedure name is not in the call stack. What can I do to figure out this name? Thank you.

Tom Kyte
June 24, 2004 - 8:59 pm UTC

you cannot.

the procedure name might not be "known" (not in the spec)
the procedure name might not be "unique" (you can have 50 procedure "p's")

you get the object (package name), the caller type, the line number in the object of that type..

so you can compute it

Matthias Rogel, June 25, 2004 - 3:24 am UTC

hallo james,

you have the line no, so you can compute the name
of the procedure / function.

it cannot known by the spec, though you also have the body
(in USER_SOURCE resp. ALL_SOURCE)

it doesn't matter that the name might not be unique.

matthias

Call stack isn't available when db procedure called from Forms

Paul Sharples, October 27, 2004 - 5:27 am UTC

Hi Tom

I'm evangelising the use of your debug package in our organisation and have hit a limitation of format_call_stack when calling db code from a Form.

Consider the following:


SQL> create table t (call_stack  varchar2(2000));

Table created.

SQL> create or replace procedure p1
  2  as
  3  begin
  4     insert into t values (dbms_utility.format_call_stack);
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure p2
  2  as
  3  begin
  4     p1;
  5     commit;
  6  end;
  7  /

Procedure created.

SQL> exec p2

PL/SQL procedure successfully completed.

SQL> select * from t;

CALL_STACK
------------------------------------------
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
365e4d1c         1  anonymous block
366b7290         4  procedure JEATKT.P1
36638a80         4  procedure JEATKT.P2
3662bea8         1  anonymous block


SQL> delete from t;

1 row deleted.

SQL> commit;

Commit complete.

SQL> 



Now, I can create a new form which contains nothing more than a single button in a single block displaying on a single canvas. The WHEN-BUTTON-PRESSED trigger contains the following code:

p2;
exit_form;



After running that, let's try the query again...

SQL> select * from t;

CALL_STACK
-------------------------------------
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
365e4d1c         1  anonymous block


SQL> 

This is only a minor irritatation as we can tailor our debug.f messages to include the module they occur in but it does kill the ability of debug.debug_it to filter output according to the debugtab.modules field (for instance, we might wish to only view debug messages emanating from within a Form).

We can get around it by judicious use of grep on the debug files (but watch out for those messages containing \n...)

Is this something we're just going to have to live with?

Many thanks. 

Tom Kyte
October 27, 2004 - 7:53 am UTC

I would call that "a product issue", doesn't seem right -- have you contacted support?

Paul Sharples, October 27, 2004 - 9:01 am UTC

Metalink to the rescue! It's a known issue, apparently, and one of the recommended workarounds is to create a table and have every program unit perform an INSERT into it to create a breadcrumb trail.

Helpfully, they go on to suggest the use of a package if one wishes to keep the stack trace private to a session.

Nice :-/

A reader, January 20, 2005 - 7:55 pm UTC


"who called me" & "who am I" procedures are Not Working for 10g

Rajesh, January 20, 2005 - 8:08 pm UTC

Hi Tom,

The "who called me" & "who am I" procedures are Not Working for 10g although it works for 9i.

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "ITAS.WHO_CALLED_ME", line 35
ORA-06512: at "ITAS.WHO_AM_I", line 8
ORA-06512: at "ITAS.DEMO", line 4
ORA-06512: at line 1

Any issues with the code..

Tom Kyte
January 20, 2005 - 11:09 pm UTC

if ( cnt = 3 ) then
line := ltrim(substr(line, instr(line, ' ' )) );
lineno := to_number(substr( line, 1, instr(line,' ') ));
line := ltrim( substr( line, instr(line,' ' ) ) );
if ( line like 'pr%' ) then


they changed the output format of the call stack, this should be less prone to that in the future.


Ammend who_called_me

eugen, November 09, 2005 - 5:49 am UTC

Hi Tom,

Could you please replace logic for the line number in the who_called_me procedure in the download section?
I did stumble over the same problem, when I accessed the code through an other thread.

TIA,
eugen

Tom Kyte
November 10, 2005 - 5:17 pm UTC

I will..

Requesting again.

Rahul, November 27, 2006 - 6:00 pm UTC

Tom,

I encountered the same (ORA-06502: PL/SQL: numeric or value error) error mentioned above when I went from 10gR1 to 10gR2.

Can you please update the code link (whenever you can) to the new code?

Thank you,
Rahul

Tom Kyte
November 27, 2006 - 8:15 pm UTC

did you, well, i don't know, consider DEBUGGING IT and seeing what the issue is and posting the fix?

Clarification

Rahul, November 28, 2006 - 11:36 am UTC

Tom,

You already gave us the better version. Anyway, I tested this snippet of code you gave in the thread couple of posts ago.

Something like this:

IF (cnt = 3)
THEN
dbms_output.put_line('This is where it is erroring out: ' || line);
line := ltrim(substr(line, instr(line, ' ')));
lineno := to_number(substr(line, 1, instr(line, ' ')));
line := ltrim(substr(line, instr(line, ' ')));
--lineno := to_number(substr(line, 13, 6));
-- line := substr(line, 21);
IF (line LIKE 'pr%')
.....

So, I tested it like this.

SELECT LTRIM
(SUBSTR
('7000000320b12c8 743 package body XX.PRL',
INSTR
('7000000320b12c8 743 package body XX.PRL',
' '
)
)
)
FROM DUAL;
SELECT TO_NUMBER
(SUBSTR
('743 package body XX.PRL',
1,
INSTR
('743 package body XX.PRL',
' '
)
)
)
FROM DUAL;

SELECT LTRIM (SUBSTR ('743 package body XX.PRL',
INSTR ('743 package body XX.PRL', ' ')
)
)
FROM DUAL;


And it works. But your link still has this code:
lineno := to_number(substr(line, 13, 6));
line := substr(line, 21);

I hope I am clear.

Shivaswamy, November 30, 2006 - 11:35 am UTC

Tom,

I get page not found for "Who called me" function. Would you mind giving the link, if it is still there?

Thanks.


Tom Kyte
November 30, 2006 - 1:57 pm UTC

remove the ~ from any links

</code> http://asktom.oracle.com/tkyte/who_called_me/index.html <code>

Sample Code so WHO_AM_I gets Func/Proc Name from w/in PKG

Jordan, March 14, 2007 - 6:43 pm UTC

This is a modified version of Tom's Who_Am_I that drills into the Call Stack and determines the FUNCTION or PROCEDURE name if the call is placed with a func/proc within a PACKAGE. The current version Tom has demonstrated only returns the owner.objectname so in the case of a package returns owner.packagename, which in my case is not helpful. I wanted a call that would dynamically get me whoami for assignment to a constant to use with generic error handling.

NOTE: I know it's a bit clunky but it seems to work well whether your proc takes arguments or not.

FUNCTION WhoAmI 
RETURN VARCHAR2 
IS

l_strOwner      VARCHAR2(30);
l_strName       VARCHAR2(30);
l_numLineNo     NUMBER;
l_strType       VARCHAR2(30);
l_strDetail     VARCHAR2(30);
l_strReturn     VARCHAR2(100);

BEGIN

  WhoCalledMe(ao_strOwner       => l_strOwner
             ,ao_strName        => l_strName
    ,ao_numLineNo      => l_numLineNo
    ,ao_strCallerType  => l_strType );
  
  l_strReturn := l_strOwner || '.' || l_strName;
  
  --This portion was an add on by Jordan to drill down further to get at the 
  --actual procedure or function name if it's inside a package.
  IF l_strType = 'PACKAGE BODY' THEN
    FOR currow in (SELECT upper(dbs.text) text
                FROM   dba_source dbs
       WHERE  dbs.owner = upper(l_strOwner)
       AND    dbs.name  = upper(l_strName)
       AND    dbs.type  = upper(l_strType)
       AND    dbs.line  < l_numLineNo
       ORDER BY dbs.line DESC) LOOP
      IF instr(currow.text,'PROCEDURE') > 0 OR
      instr(currow.text,'FUNCTION') > 0 THEN
     
  IF instr(currow.text,'(') > 0 THEN
    l_strDetail := ltrim(rtrim(substr(currow.text
                                    ,instr(currow.text,' ')
                                    ,(instr(currow.text,'(')- instr(currow.text,' ')))));
    l_strReturn := l_strReturn ||'.'||l_strDetail;  
    EXIT;
  ELSE
    l_strDetail := ltrim(rtrim(substr(currow.text
                                    ,instr(currow.text,' ')
                                    ,(length(currow.text)))));
    l_strReturn := l_strReturn ||'.'||l_strDetail;
    EXIT;
  END IF;
   END IF; 
 END LOOP;
  END IF;
       
RETURN l_strReturn;
  
END WhoAmI;

Tom Kyte
March 15, 2007 - 9:15 am UTC

well, since procedure names do not have to be on the same line as the word procedure/function

and since there could be 50 procedure P's in a package

I myself will stick with the very simple and absolutely dead on accurate.....

line number.

How about $$PLSQL_UNIT

Rahul, July 12, 2007 - 1:26 pm UTC

Tom,

I was browsing thru Laurent Schneider's website and came across this link:

http://laurentschneider.com/wordpress/2007/07/vsession_longops-in-10gr2.html

Anyway, I thought of something and wanted to know what you think.

I know you stick by the line_number absolutely, but, as an added help, can we use $$PLSQL_UNIT to figure out what is the name of the procedure or function you are at, right now?

Like this:

CREATE PROCEDURE rahul AS
BEGIN
dbms_output.put_line('Iam in : ' || $$PLSQL_UNIT);
END;

XXDAN@CRP1 > exec rahul;
Iam in : RAHUL

PL/SQL procedure successfully completed.



What are your thoughts?

Thank you.
Tom Kyte
July 12, 2007 - 2:21 pm UTC

sure, that is a new 10gr2 feature. You can use that if you like.

Never Mind.

Rahul, July 12, 2007 - 1:33 pm UTC

Tom,

Never mind. It gives you the package name but, not the procedure name within the package (if you are in a package - which you should be).

I ran this:

XXDAN@CRP1 > create or replace package mark as
procedure cup;
end;
2 /

Package created.

XXDAN@CRP1 > create or replace package body mark as
procedure cup
as begin
dbms_output.put_line('Iam in : '|| $$PLSQL_UNIT);
end;
end;
2 /

Package body created.

XXDAN@CRP1 > exec mark.cup;
Iam in : MARK

PL/SQL procedure successfully completed.


It doesn't say 'CUP' . It says 'MARK'.

So, I will still stick with line_number.

Thank you,
Rahul.

Correct your who_called_me

Sandro, August 31, 2007 - 8:08 am UTC

Hi Tom,
why you have not corrected who_called_me procedure at link http://asktom.oracle.com/tkyte/who_called_me/who.sql with suggestion on http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1529405950004#77751124628477
that correct possible error ORA-06502: PL/SQL: numeric or value error?

Package Caller

Janel, June 18, 2008 - 7:14 pm UTC

Hello Tom,

I have been working with the 'owa_util.who_called_me' procedure, which I think is very similar to your who_called_me procedure. I have it such that it is called from my package fa_misc_pkg.monthly_backup which is under the XXFA schema, and correctly returns XXFA as the owner and FA_MISC_PKG for the caller.

I would like to be able to capture the schema/user that is actually doing the execution? For example, say I am logged in as user 'XXPA' and calling the XXFA function. How can I programmatically tell that 'XXPA' is doing the executing?

I've discovered that I can use the V$SESSION view to get the username(see below) - is it as simple as that? I am not very familiar with V$SESSION, so can you please tell me if this would be acceptable, or if there is a 'more correct' way to do this?

select username
from v$session
where userenv('SESSIONID') = audsid

Thank you,
Janel
Tom Kyte
June 19, 2008 - 9:56 am UTC

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions165.htm#i1038176

sys_context('username', 'session_user' )
sys_context('username', 'current_schema' )

will be of interest to you

Janel, June 19, 2008 - 10:35 am UTC

PERFECT!

Thank you very much!!
Janel

Parsing for the procedure name

Richard, September 08, 2008 - 5:56 pm UTC

Tom,
I have followed the discussion in this and another thread about the inability of PL/SQL code to obtain its calling procedure name when the calling procedure is inside a package. You say that the package name and line number are useful enough, and perhaps they are for some purposes, such as quick tracking of a bug. But, there seem to be other purposes where that procedure name would be very useful. Looking at a log file or a stack trace containing meaningful procedure names calling each other is more informative to me than looking at one with line numbers calling each other. I might want to use the information for meta-code purposes like coverage analysis, automated checking of coded log statements, or, well, whatever.
One of your correspondents sought to pull the procedure name out of dba_source, and you pointed out a couple of problems with his work, such as overloaded procedure names and multiline statements. But, I would like to ask whether you think the concept could work after all, if enough attention were paid to such details.
My approach might be to preprocess the rows of dba_source upon initialization of a package. This would only happen once, so it could be slow, ie. parsing through the lines, checking for the opening and closing of strings, maybe using procedure signatures to assign different names to like-named procedures (as is probably done by the compiler anyway). The results could go into an associative array, such that a request could be made for the procedure name associated with a given line number. These would be available for logging purposes, whether or not an error occurs.
Does this approach seem workable? Does it sound useful? I ask before I actually try these ideas out in code.
Tom Kyte
September 09, 2008 - 7:31 am UTC

... I might want to use the
information for meta-code purposes like coverage analysis, automated checking
of coded log statements, or, well, whatever.
..

there is a code profiler that actually does that, you wouldn't want to do that yourself - dbms_profiler already exactly provides that functionality.


... But, I would like to ask
whether you think the concept could work after all, if enough attention were
paid to such details.
...

obviously it could - I mean, we wrote a plsql parser to compile it, you could to - somewhere between a parser robust enough to compile the code and their simple attempt is code that could to it reliably .


.... My approach might be to preprocess the rows of dba_source upon
initialization of a package. ....


seems like taking an atom bomb to crack a walnut. I would postpone this task that almost never needs to be done to the "reporting time"

That is, log your line numbers and unit names. IF someone actually generates a report from this log LATER - you do the work then. Do not penalize your online users with a process that does nothing for them and is probably never used in real life (during testing maybe, during production - hardly ever)

broken link

Geert, September 28, 2009 - 2:03 am UTC

Hi Tom,

A very useful topic and I wanted to check out the code you refer to however the provided link ( http://asktom.oracle.com/~tkyte/who_called_me/index.html ) results in a 404 :(

Through google cache I can still find the Sep 7th page, but then again the link to the actual sql results in a 404 as well...

Is it intentional?
Geert
Tom Kyte
September 30, 2009 - 7:06 am UTC

I'm working on reposting that stuff

when I moved from my server to apex.oracle.com - I forgot about my public_html file system stuff - really old stuff that's been there forever. I'll be posting it in apex as a "file".

soon -hopefully.

cached

Duke Ganote, September 30, 2009 - 11:41 am UTC

#bSearch engines like google should have a cached copy if you need it now.

Self referencing to Program unit in Package.

Praveen, February 10, 2011 - 10:00 am UTC

Hi Tom,
$$PLSQL_UNIT variable gives the name of the procedure/function. However if I use it in the Packaged proc/function, it just returns the package name. Is there another variable to references to exact proc/function name in the Package?

Thank you,
Praveen
Tom Kyte
February 10, 2011 - 5:03 pm UTC

there is not, I would suggest using the line number - as that is unambiguous.


There can be many procedure "P's" in a package - having the package name and line number is useful, having the procedure name isn't in general.

Is there a self-reference to my PL/SQL program's name?

gordon currie, May 27, 2011 - 9:23 am UTC

Quite a few people seem interested, as I am, in the ability to get access to the actual proc names etc. in packages. Similar to reflection in .NET in java.

I have been developing a package that does something similar. It returns line number, proc/function name and call stack etc. if anyone is interested (not 100% production quality yet but close)
Tom Kyte
May 27, 2011 - 11:01 am UTC

it would be rather trivial with builtin functions already...

ops$tkyte%ORA11GR2> create or replace function where_am_i return varchar2
  2  as
  3          l_owner varchar2(30);
  4          l_name  varchar2(30);
  5          l_lineno number;
  6          l_caller_t varchar2(255);
  7  begin
  8          owa_util.who_called_me( l_owner, l_name, l_lineno, l_caller_t );
  9  
 10          return '"' || l_owner || '"."' || l_name || '" on line ' || l_lineno;
 11  end;
 12  /

Function created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_output.put_line( where_am_i );
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          dbms_output.put_line( where_am_i );
  3          p;
  4  end;
  5  /
""."" on line 2
"OPS$TKYTE"."P" on line 4

PL/SQL procedure successfully completed.



As long as APEX is installed (and it should be by default these days) you have owa_util and the who_called_me routine/

anonymous blocks

Don Biddle, July 08, 2011 - 2:14 pm UTC

Hi Tom,

I have created an error handling procedure that uses DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to print in a log the 5 lines before and 5 lines after for each stored database object from the above. But, if the call_stack references an anonymous block at line 4 where can I find the lines of code before and after line 4 for the ANONYMOUS Block.

Thanks in advance
Tom Kyte
July 08, 2011 - 3:25 pm UTC

you'd have to get the anonymous block out of v$sql and parse it, we do not store them in the dictionary anywhere.

probably easiest just to grab the entire anonymous block and store it as a clob when you encounter it.

WHO_AM_I and WHO_CALLED_ME

garbuya, November 04, 2011 - 12:53 pm UTC

Look at this post https://forums.oracle.com/forums/thread.jspa?threadID=1049771&start=0&tstart=0

This function can handle local and overloaded functions and will return the names of calling objects up to the top no matter how deep the call to your object is
Tom Kyte
November 07, 2011 - 10:23 am UTC

I'm still of the mindset that you just want the line number. It is trivial to get the function/procedure name from that.

The performance hit of reading the dictionary is just way too large. And your code is too easily tricked (wrapped code for example), or just normal plsql code:

ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3  
  4          function foo return number;
  5  
  6  end;
  7  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3          g_x number := foo;
  4  
  5  function foo return number
  6  is
  7          function bar return number;
  8          l_x number := bar;
  9  
 10          function bar return number
 11          is
 12                  procedure foo_bar
 13                  is
 14                  begin
 15                          dbms_output.put_line( 'I should be foo_bar, I am ' || fn_who_am_i );
 16                          null;
 17                  end;
 18          begin
 19                  dbms_output.put_line( 'I should be bar, I am ' || fn_who_am_i );
 20                  foo_bar;
 21                  return 0;
 22          end;
 23  
 24  begin
 25          dbms_output.put_line( 'I should be foo, I am ' || fn_who_am_i );
 26          l_x := bar;
 27          return l_x;
 28  end;
 29  
 30  begin
 31          dbms_output.put_line( 'I should be my_pkg, I am ' || fn_who_am_i );
 32  end;
 33  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_output.put_line( my_pkg.foo );
I should be bar, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be foo_bar, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be foo, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be bar, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be foo_bar, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be my_pkg, I am UNKNOWN
I should be bar, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be foo_bar, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be foo, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be bar, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
I should be foo_bar, I am FUNCTION OPS$TKYTE.MY_PKG.FOO
0

PL/SQL procedure successfully completed.

using owa_util to get the proc / func name

Hemanshu Sheth, December 21, 2011 - 7:55 am UTC

Hi Tom,
My application front end is vb.net & backend being oracle 9i. I have one table where inserts are happening from many places. From some fn/proc, there are inserts happening with wrong data.
So, in order to trace the same, I have written a before insert trigger to get the name of fn/proc which is inserting in the table in question, firing this trigger. I used owa_util.who_called_me function but all the 4 output values are returning null.
I tried with $$PLSQL_UNIT and also with sys_context('userenv','module' or 'current_sql') but only blank values are returned.
What is the reason. Are the fn/proc from .net environment not getting picked up by above methods. Is there any other way or I am missing something?

Thnx & Rgrds
Hemanshu Sheth
Tom Kyte
December 21, 2011 - 11:03 am UTC

see below...

using owa_util to get the proc / func name

Hemanshu Sheth, December 21, 2011 - 10:08 am UTC

Thnx for quick reply. But my query continues. I am DBA but a novice. Pl bear with me if I sound silly.

1. In ur example, u invoked the trigger by insert command from the command prompt, hence it gave the object name as "T" since trigger is called directly. If u exec p1 then as p1 calls T, would it show "p1" or "T". And if we exec p2 then as p2 calls p1 calls T, would it show "p2" or "p1" or "T"?

2. Regarding using MODULE, u r right in saying that the respective procedure itself should populate the MODULE name. But, as our application source code is huge & almost all the original developers have left, it is very difficult / time consuming by present developers to trace the entire sourcecode & update each & every occurrence of this insert. If that could be done then there was no need of trigger & we would have directly rectified that particular proc/fn which is inserting bad data. I thought as we had in old DOS programming like Clipper, there was a fn called PROCNAME(p) which used to give proc name based on parameter p. If p=0 then current proc, if 1 then prev proc, if 2 then proc which called prev proc & so on. Is such functionality available here?

3. Regarding the exception, I can locate the bad data but only after it is inserted in the table. I am at the users site & not at the developers site. I can only observe data through tools like TOAD. In such case, how to raise an exception? Data is bad in the sense that one column (Store name) is getting inserted by value of different store although it fulfills all necessary constraints.

Requesting you to pl reply for all 3 points as I am eager to improvise on my knowledge & also identify the proc which is inserting wrong store name.

Thnx in anticipation..
Hemanshu
Tom Kyte
December 21, 2011 - 11:04 am UTC

ur?
u?

is your keyboard broken? It is not very professional to use that sort of "instant message speak" in forums and such. It makes you sound like a young child, not a professional developer



You are correct, that was not smart of me.



Here is the "better" example and demonstrates how to use dbms_utility to get everything you need:


ops$tkyte%ORA11GR2> create or replace procedure p1
  2  as
  3  begin
  4          insert into t values ( 1 );
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA11GR2> create or replace procedure p2
  2  as
  3  begin
  4          p1;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA11GR2> create or replace trigger t
  2  before insert on t for each row
  3  begin
  4          dbms_output.put_line( dbms_utility.format_call_stack );
  5  end;
  6  /

Trigger created.

ops$tkyte%ORA11GR2> exec p2
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x21c57434         2
OPS$TKYTE.T
0x21ba6308         4  procedure OPS$TKYTE.P1
0x223e0174         4  procedure OPS$TKYTE.P2
0x20a89958
1  anonymous block


PL/SQL procedure successfully completed.




2) see #1

3) then how is a trigger going to do anything?

None of the links work. Shame shame

Nirvana, July 13, 2013 - 12:05 am UTC

None of the links given in the page works. First of all who_am_i returns only the package name not the procedure/function name in it. What a shame! Even the very basic features is not in Oracle but cliams to be number one. What a shame! Try to give easy way to find the package.procedure name at least in 15c relase (I know it is not going to happen in the next relase).
Tom Kyte
July 16, 2013 - 4:30 pm UTC

you could not have been more wrong I guess.

shame shame shame on who?

http://asktom.oracle.com/Misc/12c-utlcallstack.html


(and yes, you are right - after 12 years some pages may have moved. A simple plugging of who_called_me into a search engine would have found them faster than you could have typed this in... .

for shame...

11g change to format_call_stack

Vinod, April 08, 2014 - 2:38 pm UTC

Something seems to have changed in dbms_utility.format_call_stack, I don't see the line numbers within packages. For example :
000007FF2D8EA520 42 procedure ATLAS.PACKAGE_DETAIL_INS_PU
000007FF335399F8 0 package body ATLAS.SH_SPLIT_SHIPMENT_PK
000007FF335399F8 406 package body ATLAS.SH_SPLIT_SHIPMENT_PK
000007FF30A971C8 4358 package body ATLAS.SH_TRANSSHIPMENT_ROLL_PK
000007FF30A971C8 5170 package body ATLAS.SH_TRANSSHIPMENT_ROLL_PK
000007FF3AE20C70 1 anonymous block

I did not see these '0' line numbers before migration.
Tom Kyte
April 16, 2014 - 5:25 pm UTC

I'm seeing the lines within the package in your output???

000007FF30A971C8      4358  package body ATLAS.SH_TRANSSHIPMENT_ROLL_PK
000007FF30A971C8      5170  package body ATLAS.SH_TRANSSHIPMENT_ROLL_PK



for example...

I'd need something to reproduce with, ran this in 11.2.0.3:

ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3          procedure p;
  4  
  5  end;
  6  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3  
  4  procedure q
  5  is
  6  begin
  7          dbms_output.put_line( dbms_utility.format_call_stack );
  8  end;
  9  
 10  procedure r
 11  is
 12  begin
 13          q;
 14  end;
 15  
 16  procedure s
 17  is
 18  begin
 19          r;
 20  end;
 21  
 22  procedure p
 23  is
 24  begin
 25          s;
 26  end;
 27  
 28  
 29  end;
 30  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec my_pkg.p
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x364345c4         7  package body OPS$TKYTE.MY_PKG
0x364345c4        13  package body OPS$TKYTE.MY_PKG
0x364345c4        19  package body OPS$TKYTE.MY_PKG
0x364345c4        25  package body OPS$TKYTE.MY_PKG
0x3197e1a8         1  anonymous block


PL/SQL procedure successfully completed.

Invalid URL

SantoshCA, April 20, 2017 - 8:06 pm UTC

Hi,

The URL given by TOM is no longer valid.

Connor McDonald
April 21, 2017 - 1:26 am UTC

owa_util.who_called_me should now suffice.

Examples are on this page, and also on this one

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4471072100346198785