RE: AW: OSUSER in V$SESSION capture in procedure?
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
fromv$session s
where sys_context('userenv', 'SESSIONID') = s.audsid;
This requires a direct grant to v_$session only.
Jared
Denham Eva [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/22/2002 11:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: AW: OSUSER in V$SESSION capture in procedure?
Wow, this worked exactly as I hoped.
Many Thanks.
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 22, 2002 8:49 PM
To: Multiple recipients of list ORACLE-L
Try:
select osuser
fromv$session
where sid in (select sid
from v$mystat);
Chk
[EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
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:[EMAIL PROTECTED]
http://www.inplan.de
-Ursprüngliche Nachricht-
Von: Denham Eva [mailto:[EMAIL PROTECTED]]
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: [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:
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:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public
RE: AW: OSUSER in V$SESSION capture in procedure?
you can also do:
select osuser
fromv$session s
where sys_context('userenv', 'SESSIONID') = s.audsid;
This requires a direct grant to v_$session only.
Jared
Denham Eva [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/22/2002 11:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: AW: OSUSER in V$SESSION capture in procedure?
Wow, this worked exactly as I hoped.
Many Thanks.
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 22, 2002 8:49 PM
To: Multiple recipients of list ORACLE-L
Try:
select osuser
fromv$session
where sid in (select sid
from v$mystat);
Chk
[EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
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:[EMAIL PROTECTED]
http://www.inplan.de
-Ursprüngliche Nachricht-
Von: Denham Eva [mailto:[EMAIL PROTECTED]]
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: [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:
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:
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
Re: AW: OSUSER in V$SESSION capture in procedure?
Try: select osuser fromv$session where sid in (select sid from v$mystat); Chk [EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Denham Eva [mailto:[EMAIL PROTECTED]] 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: [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: 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: 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).
RE: AW: OSUSER in V$SESSION capture in procedure?
Wow, this worked exactly as I hoped. Many Thanks. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 8:49 PM To: Multiple recipients of list ORACLE-L Try: select osuser fromv$session where sid in (select sid from v$mystat); Chk [EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Denham Eva [mailto:[EMAIL PROTECTED]] 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: [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: 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: 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). # This e-mail message has been scanned for Viruses and Content
