Re: PL/SQL and v$session

Why so complicated?
Why don't you use

select sid
from v$session
where audsid = userenv('sessionid')

This will work for all users, except for sys. Also, the v$session is a synonym for a view you have privilege on using a role. You'll need direct privilege, or the use the invokers right pragma in 8i.
Grant select on v$_session to <your user>

Hth,

Sybrand Bakker, Oracle DBA

"Peter Smed Vestergaard" <Peter_Vestergaard_at_saxotech.com> wrote in message news:yFi95.104$m12.3341_at_news.get2net.dk...
> Can anybody help me?
>
> How can I access v$session from PL/SQL? In an experiment I made the
> following package, using a package INFO which encapsulates some of the
> functionality in DBMS_APPLICATION_INFO.
>
> The trick would be to update v$session using INFO and then locate the row
 in
> v$session belonging to this session by looking up for that value.
>
> What have I done wrong?
>
> create or replace package body TEST is
>
> function GetUniqueSessionID return varchar2 is
> begin
> return DBMS_SESSION.UNIQUE_SESSION_ID;
> end;
>
> function GetSID return number is
> aSID number;
> aUSID varchar2(24);
> oldAction char;
> begin
> oldAction := INFO.GetAction;
> aUSID := GetUniqueSessionID;
> INFO.SetAction(aUSID);
>
> -- This select does not work
> select v$session.sid into aSID
> from v$session
> where v$session.action=aUSID;
>
> INFO.SetAction(oldAction);
> return aSID;
> exception
> when others then
> INFO.SetAction(oldAction);
> return -1;
> end;
>
> end TEST;
>
>
> --
> Peter Smed Vestergaard
> System Developer
> SAXoTECH A/S http://www.saxotech.com
> Hobrovej 42D, 9000 Aalborg
> EMail: Peter_Vestergaard_at_saxotech.com
> Private: Peter_Vestergaard_at_mail1.stofanet.dk
> ---
> Remove xxx on reply
>
>
Received on Fri Jul 07 2000 - 00:00:00 CDT