Re: OSUSER in V$SESSION capture in procedure?

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).