| |
|
08 Jan 2007 - Environmental Values |
| |
/*
-- Getting the value of an environmental variable on an Oracle
server using PL/SQL is not a
-- common problem faced by Oracle developers but I stumbled on a
procedure newly added in 10g
-- in the still undocumented DBMS_SYSTEM package that does
exactly this,
--
-- The code sample below will only really run as SYS, as it
requires execute on DBMS_SYSTEM,
-- which being undocumented is not executable by any other user
by default, so it's
-- just intended as a demo. The first parameter in the call to
get_env is the environmental variable
-- which we are interested in (eg ORACLE_HOME). The second
parameter will contain the value
-- of this variable as found by the package.
--
-- This procedure appears to raise no exceptions, passing an
invalid environmental variable,
-- simply results in a NULL val (the second variable in the call
to get_env).
--
-- Testing shows that the environmental variables whose values
can be retrieved does not vary according
-- to the logged in user (implying the package is actually
retrieving the value using an OS
-- API executed by the user running the Oracle software, eg
oracle on *nix or Administrator on Windows).
-- However, this package also seems to be able to get values for
certain variables even when not defined.
-- For example, on Windows, I can retrieve a value for
ORACLE_HOME even though this is not set as an
-- variable on my system...! I suspect the implementation is
strongly OSD and in the case of Windows
-- this package is also peeking in the registry key appropriate
for that install of Oracle.
--
-- This code has been tested and works as intended on Oracle
10104 EE.
--
-- Get this tip and others, along with useful PL/SQL utilities
at
-- www.chrispoole.co.uk
--
*/
declare
var varchar2(32767) := 'ORACLE_HOME';
val varchar2(32767);
begin
dbms_output.enable(100000);
dbms_system.get_env(var, val);
dbms_output.put_line(substr('The value of '||var||' is: '||val,
1, 255));
end;
/
|
| |
|
|