Value of %ORACLE_HOME%

Can someone tell me how to get the value of %ORACLE_HOME% or its equivalent from within a PL/SQL procedure.

LVL 10
Who is Participating?
SDuttaConnect With a Mentor Author Commented:
OK I think I found something simple that works for me on all releases that I care for.
Just need to take the name of the dll out from the end.

select file_spec from DBA_LIBRARIES where library_name = 'DBMS_SUMADV_LIB';

Will withdraw the question. Thanks Seazodiac and Grim_toaster for your inputs.

Not trying to make this complicated, but the only way I would do is to use a java stored procedure since ORACLE_HOME is really an environment variable varying from platform to platform.

but I can tell you are using windows since you use % sign.

Here is an attempt from me:

In Oracle database sqlplus (of course you have to have JAVA enabled in the database)

---1.first create a java class in oracle database
SQL>create or replace and compile java source named "EnvLister"
import java.util.Properties;

public class EnvLister extends Object

    public static void list_env()
        Properties envLists = new Properties();
        String env_name = "ORACLE_HOME";
        try {
               // use "/bin/env" on Unix
        } catch (Throwable t) {t.printStackTrace();}

        System.out.println("\n\n" + env_name
                         + " = " + envLists.get(env_name) );


----create a PL./SQL procedure to wrap around java class
SQL> create or replace procedure env_list_proc  
          AS language java name 'EnvLister.list_env()';

---Test it out

SQL>exec env_list_proc;

this should give your value of ORACLE_HOME.

I have not tested my code yet, but just give you an idea.
SDuttaAuthor Commented:

Well, I guess that can be used to get the environment variable if it exists. What if the evironment variable is not set. Subsequent to release 8i the setting of ORACLE_HOME in the environment has been deprecated. See metalink note

Is the only way left is to read the registry ? I was hoping to query some simple view in the database that gets loaded at runtime.

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Not true in Unix. you still have to set Oracle_HOME in UNIX. you have tested this out yourself.

I agreed with you in windows family.
As the value isn't probably going to change (mind you, if you have a cluster with different install paths...) you could create a directory reference in Oracle and reference it:

CREATE DIRECTORY ORACLE_HOME AS 'd:\ORA9'; (or whatever it is...)

And then at any later date access it as a directory.  (for further details, check out the Oracles SQL Reference manual).  Or, you could access it through the user/all_directories, but I'm unsure how efficient that will be!
Interesting!!! what an attempt!
I thought you would have had to go out of your way to find "ORACLE_HOME" in the data dictionary.

akramrykProject ManagerCommented:
A simple thing with  dbms_system.get_env n 10g

var_home     Varchar2(100);
var_sid     varchar2(100);


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.