Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL SYSTEM FUNCTION
PL/SQL SYSTEM FUNCTION [message #37590] Thu, 14 February 2002 22:36 Go to next message
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 #37601 is a reply to message #37590] Fri, 15 February 2002 04:01 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
i didnt get ur first question. could u explain little bit more?

2)
select username,count(*) from v$session where username is not null
group by username having count(*)>1;
Re: PL/SQL SYSTEM FUNCTION [message #37603 is a reply to message #37590] Fri, 15 February 2002 04:43 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Execute Immediate n ..
Next Topic: Date function
Goto Forum:
  


Current Time: Thu Mar 28 12:55:37 CDT 2024