Home » SQL & PL/SQL » SQL & PL/SQL » can a procedure identify itself? (Oracle 10g)
icon5.gif  can a procedure identify itself? [message #410665] Mon, 29 June 2009 10:22 Go to next message
shailendrakr
Messages: 2
Registered: June 2009
Junior Member
I am looking for a way to identify the name of the procedure from within that procedure, something like $0 of Unix. Is this possible?
I need to log errors in a table, and want to identify the procedure that caused the error. Other options are are welcome.

Thanks!
Re: can a procedure identify itself? [message #410666 is a reply to message #410665] Mon, 29 June 2009 10:26 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
http://asktom.oracle.com/tkyte/who_called_me/index.html
Re: can a procedure identify itself? [message #410667 is a reply to message #410665] Mon, 29 June 2009 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

GOOGLE is your friend but only when you actually use it.

http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html
Re: can a procedure identify itself? [message #410687 is a reply to message #410665] Mon, 29 June 2009 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create or replace procedure p is
  2  begin
  3    dbms_output.put_line('I am '||$$PLSQL_UNIT||' at line '||$$PLSQL_LINE);
  4  end;
  5  /

Procedure created.

SQL> exec p
I am P at line 3

PL/SQL procedure successfully completed.

Regards
Michel
Re: can a procedure identify itself? [message #411271 is a reply to message #410687] Thu, 02 July 2009 23:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Now that is useful. How stupid of me never to think of using conditional compilation like that.

Thanks Michel.

Kevin
Re: can a procedure identify itself? [message #411313 is a reply to message #410665] Fri, 03 July 2009 01:41 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Michel,

In which version of oracle will that procedure you mentioned execute ? I am getting some error message when try to execute,

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/37     PLS-00103: Encountered the symbol "$" when expecting one of the
         following:
         ( - + case mod new null <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> avg
         count current max min prior sql stddev sum variance execute
         forall merge time timestamp interval date
         <a string literal with character set specification>
         <a number> <a single-quoted SQL string> pipe
         <an alternatively-quoted string literal with character set
         specification>
         <an alternatively-quoted SQL string>

LINE/COL ERROR
-------- -----------------------------------------------------------------


Version
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
Re: can a procedure identify itself? [message #411315 is a reply to message #411313] Fri, 03 July 2009 01:48 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
http://otn.oracle.com/pls/db102/print_hit_summary?search_string=PLSQL_UNIT

10.2 and up.
This is why I always say: "Always post your Oracle version (4 decimals)".

Regards
Michel
Previous Topic: Multiple Search (merged 2) - 10g
Next Topic: Run/execute/compile database object
Goto Forum:
  


Current Time: Sat Feb 15 00:53:29 CST 2025