Home » SQL & PL/SQL » SQL & PL/SQL » how to get the procedure name inside the package body (oracle)
how to get the procedure name inside the package body [message #418704] Tue, 18 August 2009 04:05 Go to next message
sandeep_thebest
Messages: 2
Registered: May 2007
Location: BOISAR
Junior Member

can anyone tell
how to get the procedure name inside the package body
Re: how to get the procedure name inside the package body [message #418707 is a reply to message #418704] Tue, 18 August 2009 04:34 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Simply put: you cannot.
Re: how to get the procedure name inside the package body [message #418722 is a reply to message #418704] Tue, 18 August 2009 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/335740/102589/?#msg_335740

Regards
Michel
Re: how to get the procedure name inside the package body [message #418791 is a reply to message #418722] Tue, 18 August 2009 09:54 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
SQL> create or replace package tst_pck
  2  as
  3    procedure myproc;
  4  end;
  5  /

Package created.

SQL> create or replace package body tst_pck
  2  as
  3    procedure myproc
  4    is
  5    begin
  6      dbms_output.put_line($$PLSQL_UNIT);
  7    end;
  8  end;
  9  /

Package body created.

SQL> set serveroutput on size 1000000
SQL> exec tst_pck.myproc
TST_PCK

PL/SQL procedure successfully completed.

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Not for procedures within packages, at least not on my version.
Re: how to get the procedure name inside the package body [message #418802 is a reply to message #418791] Tue, 18 August 2009 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You're right, just object level is shown not subobject.
Rest to use a package variable to do it...

Regards
Michel
Re: how to get the procedure name inside the package body [message #418979 is a reply to message #418704] Wed, 19 August 2009 08:16 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Only way i know to get the name of the current procedure inside a package is writting your own procedure that will use the dbms_utility.format_error_stack or format_error_backtrace and analyze it to extract the current package.procedure.

Tom Kyte already have written something like this. don`t remember if it work for package but you could try it

http://asktom.oracle.com/tkyte/who_called_me/index.html
Re: how to get the procedure name inside the package body [message #418981 is a reply to message #418979] Wed, 19 August 2009 08:34 Go to previous message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No, it has the same drawbacks than the other one: it does NOT give procedure inside package, just the package itself
SQL> create or replace package body tst_pck
  2  as
  3    procedure myproc
  4    is
  5    begin
  6        dbms_output.put_line(dbms_utility.format_call_stack);
  7    end;
  8  end;
  9  /

Package body created.

SQL> exec tst_pck.myproc
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
21CCB080         6  package body MICHEL.TST_PCK
21CCA94C         1  anonymous block


PL/SQL procedure successfully completed.

Or with Tom's procedure:
SQL> create or replace package body tst_pck
  2  as
  3    procedure myproc
  4    is
  5    begin
  6        dbms_output.put_line(who_am_i);
  7    end;
  8  end;
  9  /

Package body created.

SQL> exec tst_pck.myproc
Package Body MICHEL.TST_PCK, line 6
PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Wed, 19 August 2009 08:36]

Report message to a moderator

Previous Topic: Having a problem with inline query with dblink
Next Topic: Help Needed, New to PLSQL
Goto Forum:
  


Current Time: Sun Jun 29 14:54:13 CDT 2025