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
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".