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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
