Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: AW: OSUSER in V$SESSION capture in procedure?

RE: AW: OSUSER in V$SESSION capture in procedure?

From: Denham Eva <EvaD_at_TFMC.co.za>
Date: Thu, 23 May 2002 23:53:30 -0800
Message-ID: <F001.0046A6B3.20020523235330@fatcity.com> 





Thanks Jared. Works great.



-----Original Message-----



Sent: Thursday, May 23, 2002 9:55 PM


To: Multiple recipients of list ORACLE-L




you can also do:



select osuser


from    v$session s


where sys_context('userenv', 'SESSIONID') = s.audsid;



This requires a direct grant to v_$session only.



Jared








Denham Eva <EvaD_at_TFMC.co.za>


Sent by: root_at_fatcity.com


05/22/2002 11:23 PM


Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: AW: OSUSER in V$SESSION capture in procedure?






Wow, this worked exactly as I hoped.


Many Thanks.



-----Original Message-----



[mailto:Chaim.Katz_at_Completions.Bombardier.com]
Sent: Wednesday, May 22, 2002 8:49 PM


To: Multiple recipients of list ORACLE-L





Try:



 select osuser


   from    v$session


   where   sid in (select sid

                from   v$mystat);





Chk






v.schoen_at_inplan.de_at_fatcity.com on 05/22/2002 01:14:52 PM



Please respond to ORACLE-L_at_fatcity.com



Sent by:    root_at_fatcity.com




To:    Multiple recipients of list ORACLE-L <ORACLE-L_at_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_at_inplan.de


http://www.inplan.de





-----Ursprüngliche Nachricht-----



Von: Denham Eva [mailto:EvaD_at_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_at_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_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:


  INET: v.schoen_at_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_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: 


  INET: Chaim.Katz_at_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_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).

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


#########


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_at_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_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: 


  INET: Jared.Still_at_radisys.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).

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


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_at_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_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 Fri May 24 2002 - 02:53:30 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US