Re: Custom env variable in Oracle

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Tue, 21 Apr 2009 21:22:55 +0100
Message-ID: <53258cd50904211322t3be844f7yeb15d2f466d83077_at_mail.gmail.com>

Dave

Thanks to
http://tardate.blogspot.com/2007/05/getting-environment-variables-on-oracle.htmlor http://tonguc.oracleturk.org/index.php/2006/09/21/how-to-get-oracle_home-from-sql-or-plsql/

DECLARE
    folder VARCHAR2(100);
BEGIN
    sys.dbms_system.get_env(’ORACLE_HOME’, folder);     dbms_output.put_line(folder);
END; SQL> connect sys/pwd as sysdba
Connected.

SQL> grant execute on dbms_system to testuser   2 /

Grant succeeded.

SQL> connect testuser/testuser
Connected.
SQL> set serverout on size 2000
SQL> DECLARE
  2 folder VARCHAR2(100);
  3 BEGIN

  4      sys.dbms_system.get_env('ORACLE_HOME', folder);
  5      dbms_output.put_line(folder);

  6 END;
  7 /
C:\oraclexe\app\oracle\product\10.2.0\server

PL/SQL procedure successfully completed.

Note of course that this will only tell you the environment of the oracle shadow process, not the environment of the calling user.

If sqlplus is the only client you are worried about, and if your users are well behaved, you could simply use a login.sql or glogin.sql which itself calls DBMS_APPLICATION_INFO.SET_CLIENT_INFO.

Regards Nigel

2009/4/21 Herring Dave - dherri <Dave.Herring_at_acxiom.com>

> Folks,
>
>
>
> I’ve got a need to somehow capture information about a session at the OS
> level and alter an Oracle’s session based on that previously found info.
> Something to the effect of checking an env. variable and setting
> DBMS_APPLICATION_INFO.SET_CLIENT_INFO accordingly. I think my only option
> is to create an external procedure that is called from a logon trigger, but
> maybe I’m missing something.
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 21 2009 - 15:22:55 CDT