From: "Peter Smed Vestergaard" <Peter_Vestergaard@saxotech.com>
Subject: PL/SQL and v$session
Date: 2000/07/07
Message-ID: <yFi95.104$m12.3341@news.get2net.dk>#1/1
X-Priority: 3
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
X-Complaints-To: abuse@uni2.dk
X-Trace: news.get2net.dk 962967262 192.38.129.130 (Fri, 07 Jul 2000 12:54:22 MET DST)
Organization: UNI2 Internet Kunde
X-MSMail-Priority: Normal
Reply-To: "Peter Smed Vestergaard" <xxxPeter_Vestergaard@saxotech.com>
NNTP-Posting-Date: Fri, 07 Jul 2000 12:54:22 MET DST
Newsgroups: comp.databases.oracle.misc


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@saxotech.com
Private: Peter_Vestergaard@mail1.stofanet.dk
---
Remove xxx on reply