Value of %ORACLE_HOME%

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"
AS
import java.io.*;
import java.util.Properties;

public class EnvLister extends Object
{

    public static void list_env()
   {
        Properties envLists = new Properties();
        String env_name = "ORACLE_HOME";
        try {
          envLists.load(  
               // use "/bin/env" on Unix
                Runtime.getRuntime().exec("C:\>set").getInputStream());
        } 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.

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 http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=70215.1&p_database_id=NOT

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.

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.

A simple thing with  dbms_system.get_env n 10g


Declare
var_home     Varchar2(100);
var_sid     varchar2(100);
BEGIN
dbms_system.get_env('ORACLE_HOME',var_home);
dbms_output.put_line(var_home);

dbms_system.get_env('ORACLE_SID',var_sid);
dbms_output.put_line(var_sid);

end;
/