3

Can anyone help me out with an PL/SQL query to get the ORACLE_HOME path in Oracle 11G?

I require it to be fetched from the Oracle DB only & not via the Operating system's environment variables.

PS: I'm a DB newbie.

1
  • 2
    Why do you need this?
    – miracle173
    Apr 9, 2015 at 9:26

2 Answers 2

6

There is sys_context function

that works for Oracle 12c

select SYS_CONTEXT ('USERENV','ORACLE_HOME') from dual;

This does not work for lower versions of Oracle. For 11g and lower versions, I don't know a simple method.

PS: I read @Balasz' answer and now I know a simple answer for version 11g.

2
  • The link to sys_context is not right. Please correct it. Apr 13, 2018 at 3:09
  • @KaushikNayakk thnx
    – miracle173
    Apr 13, 2018 at 7:36
3
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> var oracle_home clob;
SQL> exec dbms_system.get_env('ORACLE_HOME', :oracle_home);

PL/SQL procedure successfully completed.

SQL> print oracle_home

ORACLE_HOME
--------------------------------------------------------------------------------
/oracle/base/product/db11204ee

But you want it in a query. This later version also allows you to grant execute just on this single function to users with lower privileges, instead of the whole DBMS_SYSTEM package:

create or replace function get_oracle_home return clob
as
  oh clob;
begin
  dbms_system.get_env('ORACLE_HOME', oh);
  return oh;
end;
/

SQL> select get_oracle_home from dual;

GET_ORACLE_HOME
--------------------------------------------------------------------------------
/oracle/base/product/db11204ee
2
  • if you will give "ps -ef |grep tns" then get all details about your Oracle_Home Apr 9, 2015 at 13:55
  • 2
    @Md Haidar Ali Khan: Even if the listener runs from the same Oracle Home, you can do this only on the database server itself. Apr 9, 2015 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.