RE: OSUSER in V$SESSION capture in procedure?

From: Kevin Lange <kgel_at_ppoone.com>
Date: Wed, 22 May 2002 12:45:38 -0800
Message-ID: <F001.004684AC.20020522124538@fatcity.com>

you can also get the sid directly (if you don't want to use any packages) from the steve adams script:

select

  p.pid,
  s.sid,
  s.serial#

from
  sys.v_$session s,
  sys.v_$process p
where
  s.sid = (select sid from sys.v_$mystat where rownum = 1) and   p.addr = s.paddr
/

from this you can lookup the pertinent info in the v$session table.

-----Original Message-----
Sent: Wednesday, May 22, 2002 2:52 PM
To: Multiple recipients of list ORACLE-L

Denham Eva wrote:
>
> Hello Listers,
>
> I have what I hope is challenging problem.
> I am trying to create a procedure that execs from a trigger on a table.
> Simple enough.
> But I want to capture the OSUSER value from v$session so that the there is
a
> history of changes to the table and by whom.
> Problem with using USER function is that all the users access the server
via
> a third party app and therefore have one username.
> Pretty pointless for this effort then, as I could update the column in the
> history table with that user and be done with it.
> But the use of UID also does not work because that brings back a whole
list
> of all the OSUSER value.
>
> ie
>
> CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE)
> IS
> V_UID NUMBER;
> V_OSUSER
> BEGIN
>
> BEGIN
> SELECT UID
> INTO V_UID
> FROM DUAL;
> END;
>
> BEGIN
> SELECT OSUSER
> INTO V_OSUSER
> FROM V$SESSION
> WHERE OSUSER := V_UID;
> END
>
> rest of procedure.....Includes insert etc
> END TEST
>
> Now obviously this returns more than one row as all the users use the same
> username through the app.
> Any suggestion?
>
> Many TIA
> Denham Eva
> Oracle DBA
> In UNIX Land
> On a quiet Night, you can hear the Windows machines reboot.
>

Matching AUDSID with SYS_CONTEXT('USERENV', 'SESSIONID') will return a single row (and the good one). Intuitive enough, isn't it.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: kgel_at_ppoone.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed May 22 2002 - 15:45:38 CDT