How to get ORACLE_HOME by SQL

I had to get Oracle alert log file path by SQL on 9i, 10g.
Because I didn't want to force a user input it.

First I tried the following SQL sentence.
SQL> select * from sysman.mgmt$software_homes;

win2k3svrvminst OraHome92 ORACLE_HOME D:\oracle\ora92

win2k3svrvminst OraDb10g_home1 ORACLE_HOME F:\oracle\product\10.2.0\db_1
win2k3svrvminst Independent Products INDEPENDENT N/A
(copied on SQL Developer)

I didn't know which ORACLE_HOME is proper one.

I should keep searching and tried this.


SQL>select substr(file_spec,   1,   instr(file_spec,   '\',   -1,   2) -1) ORACLE_HOME
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB';
(On Unix, substitute with '/'  )

This SQL showed me ORACLE_HOME, C:\oracle\product\10.2.0\db_1, but it required me as sysdba on 10g.

Because my application can't get sysdba's id and password, I had to keep searching.


SQL> set autopri on
SQL> var oracle_home varchar2(255)
SQL> exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)

It works too, but I don't want to use PL/SQL procedure.

Finally, I quited searching.
But this article can help some people who want to know  "How to get ORACLE_HOME by using SQL".