Home » SQL & PL/SQL » SQL & PL/SQL » System variable for the current procedure/function (10g)
System variable for the current procedure/function [message #347845] Sun, 14 September 2008 09:32 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
Hi,

Is there a system variable to know the current procedure? For instance, if I am running inside procedure MyProc. And I would like to display the current procedure, can I have something like:
dbms_output.put_line(sys_proc);

where sys_proc is a system-defined variable (like 'user','sysdate')? Else, I would have to hard-code it as:
dbms_output.put_line('MyProc');


Thanks in advance.


Re: System variable for the current procedure/function [message #347846 is a reply to message #347845] Sun, 14 September 2008 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

visit http://asktom.oracle.com & do a keyword search for whoami
Re: System variable for the current procedure/function [message #347853 is a reply to message #347845] Sun, 14 September 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace procedure p is
  2  begin
  3    dbms_output.put_line('PL/SQL Unit: '||$$PLSQL_UNIT||' line: '||$$PLSQL_LINE);
  4  end;
  5  /

Procedure created.

SQL> exec p
PL/SQL Unit: P line: 3

PL/SQL procedure successfully completed.

Regards
Michel
Re: System variable for the current procedure/function [message #347902 is a reply to message #347853] Mon, 15 September 2008 00:28 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Whoa! No kidding! I didn't know that! Cool! /forum/fa/3958/0/
Re: System variable for the current procedure/function [message #347904 is a reply to message #347853] Mon, 15 September 2008 00:39 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

@Michel,

Is this system varibale ($$PLSQL_UNIT,$$PLSQL_LINE) are only available from Oracle 10G version?

I ran this on Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production and getting the compilation error.

SQL> create or replace procedure p is
2 begin
3 dbms_output.put_line('PL/SQL Unit: '||$$PLSQL_UNIT||' line: '||$$PLSQL_LINE);
4 end;
5 /

Warning: Procedure created with compilation errors.

SQL> show error;
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/45 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
Re: System variable for the current procedure/function [message #347908 is a reply to message #347904] Mon, 15 September 2008 00:58 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, there are new in 10g.
Before you have to use "whoami" Tom Kyte's procedure.

Database PL/SQL User's Guide and Reference
Chapter 2 Fundamentals of the PL/SQL Language
Section Conditional Compilation
Paragraph Using Predefined Inquiry Directives With Conditional Compilation (and related)

Regards
Michel
Previous Topic: insert into clustered table
Next Topic: select from Index-By Table in a package has errors
Goto Forum:
  


Current Time: Fri Mar 29 10:13:55 CDT 2024