PL/SQL and v$session

From: Peter Smed Vestergaard <Peter_Vestergaard_at_saxotech.com>
Date: 2000/07/07
Message-ID: <yFi95.104$m12.3341@news.get2net.dk>#1/1

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