Message-ID: <F001.0046819B.20020522104858@fatcity.com>
Date: Wed, 22 May 2002 10:48:58 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Chaim.Katz@Completions.Bombardier.com
Subject: Re: AW: OSUSER in V$SESSION capture in procedure?
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


Try:

 select osuser
   from    v$session
   where   sid in (select sid
                from   v$mystat);

Chk


v.schoen@inplan.de@fatcity.com on 05/22/2002 01:14:52 PM

Please respond to ORACLE-L@fatcity.com

Sent by:    root@fatcity.com


To:    Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
cc:


Hi Eva,

1. If there is always the same osuser, why don't you use a default value.
2. Your proc couldn't work, because
Select uid from dual;
UID Gives you the USER_ID (number) from all_users.
Then you compare OSUSER (char) with UID(number)?

This should work:

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 USERNAME
         INTO V_OSUSER
       FROM ALL_USERS
       WHERE USER_ID := V_UID;
      END

rest of procedure.....Includes insert etc
END TEST

HTH

Volker Schoen
E-Mail: mailto:v.schoen@inplan.de
http://www.inplan.de


-----Ursprüngliche Nachricht-----
Von: Denham Eva [mailto:EvaD@TFMC.co.za]
Gesendet: Mittwoch, 22. Mai 2002 17:34
An: Multiple recipients of list ORACLE-L
Betreff: OSUSER in V$SESSION capture in procedure?


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.


############################################################################

#########
This e-mail message has been scanned for Viruses and Content and cleared
by MailMarshal
For more information please visit www.marshalsoftware.com
############################################################################

#########
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denham Eva
  INET: EvaD@TFMC.co.za

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@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:
  INET: v.schoen@inplan.de

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@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: 
  INET: Chaim.Katz@Completions.Bombardier.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@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).