RE: OSUSER in V$SESSION capture in procedure?

One thing that is possible .... to get a unique system id you can allways do the following:

When the user logs into your application, place a record in a table stating all the pertinent info like application, machine id, date/time, etc. Allowing with this info use the DBMS_SESSION package and get the UNIQUE IDENTIFIER for the user. You can do:
  select dbms_session.unique_session_id into unique_identifier from dual;

For all further updates/etc use this variable in the update of an IDENTIFIER column.

If you want to know who did the update then, take the IDENTIFIER column and match it up to the information in the LOGIN table you added to at the begining of the session.

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