How to get ORACLE_HOME from sql or pl/sql
1- The following commands will work for versions 8, 9 and 10.
– Oracle 8i, 9i (Unix + Windows)
select NVL(substr(file_spec, 1, instr(file_spec, ‘\’, -1, 2) -1) , substr(file_spec, 1, instr(file_spec, ‘/’, -1, 2) -1)) folder
from dba_libraries
where library_name = ‘DBMS_SUMADV_LIB’
2- The get_env function was introduced in 10g although the DBMS_SYSTEM package exists since 8i. The only problem is that the DBMS_SYSTEM.get_env requires sysdba privileges which you may not have.
– Oracle 10g
DECLARE
folder VARCHAR2(100);
BEGIN
sys.dbms_system.get_env(’ORACLE_HOME’, folder);
dbms_output.put_line(folder);
END;
3- You may use java SP but you must count that the user installed the java option.
Bence 8i ve 9i için ortam değişkenlerini okumanın en güvenilir yolu C çağrısı yapmak;
– C kodu
#include
#include
#include
char *getORACLE_HOME(void);
char *getORACLE_HOME(void) {
char *pOracleHome, *pOracleHome_tmp ;
pOracleHome_tmp = getenv(”ORACLE_HOME”);
if ((pOracleHome = (char *) malloc(strlen(pOracleHome_tmp) + 1)) == (char *) NULL)
return (char *) NULL ;
return strcpy(pOracleHome, pOracleHome_tmp);
}
– C kodunu kütüphane olarak derliyoruz.
gcc -c -fPIC libenv.c
gcc -shared -fPIC -o libenv.so libenv.o
– Derlediğimiz kütüphaneyi veritabanı üzerinde tanımlıyoruz.
create or replace library ENV_EXT_LIB as ‘/export/home/oracle/SHUDB/scr/libenv.so’;
/
CREATE OR REPLACE FUNCTION GET_ORACLE_HOME
RETURN VARCHAR2 IS
LANGUAGE C
NAME “getORACLE_HOME”
LIBRARY sysadm.env_ext_lib ;
/
SQL> select get_oracle_home from dual ;
GET_ORACLE_HOME
——————————————————————————–
/global/ora920/app/oracle/product/920
SQL>
Kolay gelsin.
Comment by Ogün Heper — 21 September 2006 @ 1:44 pm