Oracle 如何确定ora_home目录在哪里使用SQL或PL/SQL？

DECLARE
  theresult varchar2(1000);
begin
  dbms_system.get_env('ORACLE_HOME', theresult);

  dbms_output.put_line('result = ' || theresult);
end;
复制

line 2: ORA-06550: line 4, column 3:
PLS-00201: identifier 'DBMS_SYSTEM.GET_ENV' must be declared
ORA-06550: line 4, column 3:
复制

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.

复制