PL/SQL SYSTEM FUNCTION [message #37590] |
Thu, 14 February 2002 22:36 |
Davide
Messages: 15 Registered: February 2002
|
Junior Member |
|
|
hi,
is there a function such as UID or USER that can return the name of the procedure,function or package in which is located? and one that can tell you how many people is logged on with the same user?
Thanks,
Davide
|
|
|
|
Re: PL/SQL SYSTEM FUNCTION [message #37603 is a reply to message #37590] |
Fri, 15 February 2002 04:43 |
Davide
Messages: 15 Registered: February 2002
|
Junior Member |
|
|
Hi Suresh,
say I have a procedure like this:
PROCEDURE PRC1 IS
BEGIN
....
DBMS_OUTPUT.PUT_LINE'From : '||??? ||' by User: '||USER|| ' and session id: '||USERENV('SESSIONID'));
END PRC1;
sql>execute PRC1;
From : PRC1 by User: Davide and session id: 88844
Is there a function ??? like USER that returns the name of the procedure(or function)?
Hope I have been clear. Thanks,
Davide
|
|
|
Re: PL/SQL SYSTEM FUNCTION [message #37609 is a reply to message #37603] |
Fri, 15 February 2002 09:23 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
1) create this function
create or replace function myname return varchar2 is
lcnt number:=0;
npos number;
v varchar2(4000);
retstr varchar2(500);
tempstr varchar2(500);
begin
v:=dbms_utility.format_call_stack;
loop
npos := instr(v,chr(10));
if lcnt=5 or npos=0 or npos is null then
exit;
end if;
retstr := substr(v,1,npos-1);
v:= substr(v,npos+1);
if lcnt=4 then
tempstr:=retstr;
end if;
lcnt := lcnt+1;
end loop;
if (instr(tempstr,'procedure')>0) then
tempstr:= substr(tempstr, instr(tempstr,'procedure'));
elsif (instr(tempstr,'function')>0) then
tempstr:= substr(tempstr, instr(tempstr,'function'));
elsif (instr(tempstr,'anonymous')>0) then
tempstr:= substr(tempstr, instr(tempstr,'anonymous'));
elsif (instr(tempstr,'package')>0) then
tempstr:= substr(tempstr, instr(tempstr,'package'));
end if;
return tempstr;
end;
2)
PROCEDURE PRC1 IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('From : '||myname ||' by User: '||USER|| ' and session id: '||USERENV('SESSIONID'));
END PRC1;
3) execute prc1
Example
SQl> created myname function
SQL> create or replace procedure proc1 is
2 begin
3 dbms_output.put_line('From '||myname||' '||user);
4 end;
5 /
Procedure created.
SQL> exec proc1
From procedure SURESH.PROC1 SURESH
PL/SQL procedure successfully completed.
|
|
|