In 10g however, there's an interesting procedure DBMS_SYSTEM.GET_ENV available which does the job:
set autoprint on
var ORACLE_HOME varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)
PL/SQL procedure successfully completed.
ORACLE_HOME
-----------------------------------------
D:\oracle\product\10.2.0\db_1
DBMS_SYSTEM is an undocumented/unsupported package. It mainly seems to be an internal utility function for debugging and event monitoring. The package itself is obfusticated, but we can discover a little about it from the data dictionary. The USER_PROCEDURES view lists the individual procedures available in the package:
select PROCEDURE_NAME from USER_PROCEDURES where OBJECT_NAME = 'DBMS_SYSTEM';
PROCEDURE_NAME
------------------------------
DIST_TXN_SYNC
GET_ENV
KCFRMS
KSDDDT
KSDFLS
KSDIND
KSDWRT
READ_EV
SET_BOOL_PARAM_IN_SESSION
SET_EV
SET_INT_PARAM_IN_SESSION
SET_SQL_TRACE_IN_SESSION
WAIT_FOR_EVENT
And USER_ARGUMENTS can tell us about the parameters. For example:
select OBJECT_NAME,ARGUMENT_NAME,POSITION,DATA_TYPE,IN_OUT
from USER_ARGUMENTS
where PACKAGE_NAME='DBMS_SYSTEM' and OBJECT_NAME='GET_ENV'
order by POSITION;
OBJECT_NAME ARGUMENT_NAME POSITION DATA_TYPE IN_OUT
------------- -------------- ---------- --------- ------
GET_ENV VAR 1 VARCHAR2 IN
GET_ENV VAL 2 VARCHAR2 OUT
Given an environment variable name (VAR), GET_ENV returns its value (VAL). These values are coming from the system environment that belongs to the Oracle server process. If you have a dedicated server config, the environment is inherited from the tnslsnr process that spawned the server process. If shared server, then the environment is inherited from whatever process (PMON? PSP0?) that started the shared server process.
So an interesting poke around in some Oracle internals, but there are lots of reasons why you shouldn't use this trick in any production situation!
- It is undocumented and unsuppported. The "get_env" method seems to have appeared in 10g, but there's also no guarantee it will be present in any future versions.
- There are better solutions. SQL client code shouldn't directly depend on server environment variables.
- Remember it is instance specific, and may be misleading in a RAC environment.
3 comments:
Often times, several unix users (whith distict user id) may share the same DB account. We want to be able to log who executed the pl/sql script. It would be great if we could greb unix userid from pl/sql package and log it. Is there a way to do it?
We are still using Oracle 9i, but will upgrade to 10g soon.
Hi anonymous!
No need for undocumented hacks for this;-)
You can query the osuser from v$session, or more directly from SYS_CONTEXT('USERENV','OS_USER').
From my experience, this is a reliable indicator of the O/S account from which the database connection originates - locally or remotely. This can also be obtained within PL/SQL packages even if procedures have been created with AUTHID DEFINER rights.
e.g.
CREATE OR REPLACE FUNCTION GET_OSUSER RETURN VARCHAR2
IS
l_return VARCHAR2(30);
BEGIN
select SYS_CONTEXT('USERENV','OS_USER')
into l_return
from dual;
return l_return;
END GET_OSUSER;
/
Hi, Your blog is really very informative and useful for me. Thanks for sharing this valuable blog.
Regards..
Unix Training
Post a Comment