Home » SQL & PL/SQL » SQL & PL/SQL » how to get the name of current Procedure? (10g r2,linux)
how to get the name of current Procedure? [message #515710] Wed, 13 July 2011 00:46 Go to next message
lzfhope
Messages: 69
Registered: July 2006
Member
hi,all
I have a procedure like below:
  create or replace procedure sp_test
  is
    vs_proc_name varchar2(40);
  begin
    --get the name of current procedure ,here is "sp_test"
    --[color=red]but ,i do not want the hard code here[/color]
    insert into test_Page(proc) values(vs_proc_name);
    ---
    --
    commit;
  end;
  

I don't want to coding like this:
     vs_proc_name:='sp_test';
  

so ,is there any building function to get that ?

thank in advance!

Re: how to get the name of current Procedure? [message #515717 is a reply to message #515710] Wed, 13 July 2011 01:00 Go to previous messageGo to next message
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check WHO_AM_I & WHO_CALLED_ME on Tom Kyte's blog.
Re: how to get the name of current Procedure? [message #515868 is a reply to message #515717] Wed, 13 July 2011 20:36 Go to previous messageGo to next message
lzfhope
Messages: 69
Registered: July 2006
Member
that's good !
thank you !
Re: how to get the name of current Procedure? [message #515894 is a reply to message #515710] Thu, 14 July 2011 02:33 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thats good link, as Tom shared in the link, it is incorporated to an Oracle package. Here is the example.


create or replace procedure called
as
   l_owner  varchar2(20);
   l_name  varchar2(20); 
   l_lineno number;
   l_caller varchar2(20);
begin 
   OWA_UTIL.WHO_CALLED_ME (l_owner, l_name,l_lineno,l_caller);
   dbms_output.put_line('You are called by '||'owner   '||l_owner||
' Calling Object '||l_name||' Line no '||l_lineno||' Caller Type '||l_caller);

end;
/


Regards,
Pointers

[Updated on: Thu, 14 July 2011 03:10] by Moderator

Report message to a moderator

Re: how to get the name of current Procedure? [message #516126 is a reply to message #515894] Fri, 15 July 2011 03:10 Go to previous messageGo to next message
lzfhope
Messages: 69
Registered: July 2006
Member
what a pity!
I have looked into the package " OWA_UTIL".

Re: how to get the name of current Procedure? [message #516175 is a reply to message #515710] Fri, 15 July 2011 06:06 Go to previous message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
For standalone stored procedures (assuming you are on at least 10g) you can use conditional compilation:

SQL> create or replace
  2    procedure p1
  3      is
  4      begin
  5          dbms_output.put_line('Stored procedure name is "' || $$PLSQL_UNIT || '"');
  6  end;
  7  /

Procedure created.

SQL> set serveroutput on
SQL> exec p1;
Stored procedure name is "P1"

PL/SQL procedure successfully completed.

SQL> 


SY.
Previous Topic: Exeception error in application
Next Topic: all rows to single rows
Goto Forum:
  


Current Time: Sun Aug 17 19:19:55 CDT 2025