Re: Trigger Help

>>select osuser from user_session where audsid = USERENV('SESSIONID');
This doesn't work in 8.0.4 because the view user_session doesn't exist (I don't have a 7.x database in front of me at the moment). The v$ (performance) views generally are not available to other schemas (they're owned by SYS). Rather than grant select on them to public or other schemas, we chose to encapsulate the query against v$session in a public function, to be used much like the USER function. -- compile under SYS schema!
CREATE OR REPLACE FUNCTION getosuser
RETURN VARCHAR2 IS
  Losuser v$session.osuser%TYPE;
BEGIN
  select osuser INTO Losuser
  from v$session
  where audsid = USERENV('SESSIONID');
  RETURN(Losuser);
END;
/
GRANT EXECUTE ON getosuser TO PUBLIC;
CREATE PUBLIC SYNONYM getosuser FOR sys.getosuser;

SQL> conn scott/tiger
Connected.
SQL> column getosuser format a30

SQL> select user,getosuser from dual;

USER                           GETOSUSER
------------------------------ ------------------------------
SCOTT                          administrator

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
clifford buetikofer wrote in message <366C1EFB.2CE62C8B_at_merck.com>...
>My 2 Cents...
>
>A while back someone gave me this SQL snippet to get the OSUser:
>
>Oracle 7.3.2
>-------------
>select osuser from user_session where audsid = USERENV('SESSIONID');
>
>
>Oracle 8.0.4
>------------
>select osuser from aps_session where audsid = USERENV('SESSIONID');
>
>Cliff
>
>
> The contents of this message express only the sender's opinion.
> This message does not necessarily reflect the policy or views of
> my employer, Merck & Co., Inc. All responsibility for the
statements
> made in this Usenet posting resides solely and completely with the
> sender.
Received on Mon Dec 07 1998 - 12:34:27 CST