How to determine ORACLE_HOME from PL/SQL?

Question

Is it possible to determine where Oracle is installed using pure PL/SQL?

Use case: In order to call an external C library, I need to know its exact path (for the create library call). My DLL will reside in Oracle's BIN directory, but I can't hard-code the path of the DB installation in my scripts...

score 3 · Answer 1 · answered Jun 19 '09 at 13:48

3

DECLARE
 RetVal VARCHAR2(100);
BEGIN
  dbms_system.get_env('ORACLE_HOME', RetVal);
  dbms_output.put_line(RetVal);
END;

NOTE: It is likely you that you will not have permission to this package by default.

answered Jun 19 '09 at 13:48

Brian

13,412
10
56
82

Unfortunately, on my database, `DBMS_SYSTEM` is not installed. – Jens Bannmann Jun 19 '09 at 14:14
Have you considered using a DIRECTORY? CREATE OR REPLACE DIRECTORY APP_ORACLE_HOME AS '/u01/oracle/10.1.2'; – Brian Jul 06 '09 at 01:57

score 2 · Accepted Answer · edited Apr 12 '18 at 23:20

2

On Windows and Oracle 10g, this works:

SELECT  
substr(file_spec,1,instr(file_spec,'\',1,3)) 
FROM dba_libraries 
WHERE library_name='DBMS_SUMADV_LIB'

edited Apr 12 '18 at 23:20

Jens Bannmann

4,845
5
49
76

answered Jun 19 '09 at 13:45

b.roth

9,421
8
37
50

score 2 · Answer 3 · answered Apr 13 '18 at 04:29

2

Starting from Oracle 12c, you can use the SYS_CONTEXT function: Oracle docs

SET SERVEROUTPUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE(SYS_CONTEXT('USERENV','ORACLE_HOME'));
END;
/

/u01/app/oracle/product/12.1.0.2/dbhome_1

answered Apr 13 '18 at 04:29

Kaushik Nayak

30,772
5
32
45

How to determine ORACLE_HOME from PL/SQL?

3 Answers3