I tried the following:
DECLARE
theresult varchar2(1000);
begin
dbms_system.get_env('ORACLE_HOME', theresult);
dbms_output.put_line('result = ' || theresult);
end;
but got the following error
line 2: ORA-06550: line 4, column 3:
PLS-00201: identifier 'DBMS_SYSTEM.GET_ENV' must be declared
ORA-06550: line 4, column 3:
I checked the Database PL/SQL Packages and Types Reference and note that there's no dbms_system package listed. I googled the problem but couldn't find any other solution.
I was able to figure out the ora_home directory by using the registry but it would nice to do this through SQL or PL/SQL.
DBMS_SYSTEM is an internal routine that is hence only granted to SYS.
I would be inclined to use an external table, eg
runenv.bat
==========
@echo off
C:\Windows\System32\cmd.exe /c "set"
SQL> CREATE TABLE t
2 ( env_name VARCHAR2(100)
3 , env_val VARCHAR2(1000)
4 )
5 ORGANIZATION EXTERNAL
6 (
7 TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY temp
9 ACCESS PARAMETERS
10 (
11 RECORDS DELIMITED BY NEWLINE
12 PREPROCESSOR temp: 'runenv.bat'
13 FIELDS TERMINATED BY '='
14 )
15 LOCATION ('empty.txt')
16 )
17 REJECT LIMIT UNLIMITED;
Table created.
(where empty.txt is a file of zero size)
SQL> select * from t;
ENV_NAME
----------------------------------------------------------------------------------------------------
ENV_VAL
----------------------------------------------------------------------------------------------------------------------------------
COMSPEC
C:\WINDOWS\system32\cmd.exe
ORACLE_HOME
c:\oracle\product\12.2.0.1
PATH
c:\oracle\product\12.2.0.1\bin;C:\ProgramData\Oracle\Java\javapath;C:\Program Files\Dell\DW WLAN Card;c:\oracle\product\12.1.0.2\b
in;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System3
2\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Progr
am Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:
\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\bin;c:\bin\usr\local\wbin;C:\Program Files (x86)\Windows Live\Sh
ared;C:\Program Files\Git\cmd;C:\Program Files (x86)\Skype\Phone\;c:\bin\ffmpeg\bin;C:\WINDOWS\system32\config\systemprofile\AppDa
ta\Local\Microsoft\WindowsApps
PATHEXT
.COM;.EXE;.BAT;.CMD;.VBS;.JS;.WS;.MSC
PROMPT
$P$G
5 rows selected.
Remember that the environment variables are those that the database started with, not those in your session