?
Solved

is there a way to find out ORACLE_HOME directory

Posted on 2003-02-21
22
Medium Priority
?
6,832 Views
Last Modified: 2013-12-11
is there a way to find out ORACLE_HOME directory
using just SQL commands?

I m using Oracle 8i and win2000
0
Comment
Question by:vachooho
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 3
  • +1
22 Comments
 
LVL 4

Accepted Solution

by:
bmoshier earned 400 total points
ID: 8000934
Right off the top of my head (and a friend's head who has been an Oracle DBA since release 6!), I can't think of a method to obtain the Oracle_Home directory just using SQL.

I was looking through the v$parameter table but (at least in my data) the values reference it using "%ORACLE_HOME%" on Windows/XP Professional.

If you don't mind using PL/SQL and an external program, you'll have a winner.  An external program can run as a PL/SQL function and return the information you desire.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0
 
LVL 5

Author Comment

by:vachooho
ID: 8010214
OK -
what i m trying to do is to get the content of "tnsnames.ora" file from the machine running database server.

If your solution fit it - you're a winner!

0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8010543
The solution can give you the Oracle_Home, but how are you going to get from there to the "tnsnames.ora" file using SQL?  If you're using PL/SQL, there is UTL.FILE but it only accesses previously specified directories in the init.ora/spfile.ora file.

So, having the ORACLE_HOME in a SQL statement / PL/SQL procedure in and of itself won't get you the tnsnames.ora file.  Unless you have UTL.FILE point to the necessary subdirectory, which could present a security problem.

I'm trying to understand what you're wanting (start to finish) to insure my suggestions/help is in the correct direction.

One possibility is to have the external routine read the tnsnames.ora file and put it into the database (e.g. into a table).

BTW, what Oracle version, hardware, operating system, and operating system version are you using?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 5

Author Comment

by:vachooho
ID: 8010603
we have utl_file_dir points to * in init.ora file

we use Oracle 8i on Windws2000 server

what we trying to do is to read tnsnames.ora file - parse it to get the list of available connect descriptors
we show them in GUI allowing user to select from it.

I am able to get the list of descriptors from local machine - where client software is installed.

In this particular case I need the list from Oracle server machine.

So far there were no problems with reading files - the only problem I found now is that there is no way to determine default Oracle home using just PL/SQL

by the way I was thinking of Java procedure also - but still have not time to look over the documentation.

0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8013836
Here is how to get the Oracle_Home into a PL/SQL varchar2 variable, where you can use it as necessary.  I got this working on Windows/XP Professional with Oracle 9.2.0.1.0.  It should work with Windows 2000.  You will need a C compiler (e.g. Microsoft Visual C++ 6.0).

1)  Compile the C program using make.bat.

make.bat

REM USAGE: just type MAKE
cl -I. /LD -Zi oraclehome.c /link msvcrt.lib /nod:libcmt /DLL

oraclehome.c

/*------------------------------------------------------------------------*\
|                                                                          |
| (c) Copyright 2003 Bertram Glenn Moshier - All Rights Reserved           |
|                                                                          |
|     Program                                                              |
|       Name:     OracleHome.c                                             |
|       Version:  1.00                                                     |
|       Date:     2003-02-24                                               |
|                                                                          |
|       Purpose:  Pass back to an Oracle funcion the Oracle_Home           |
|                 environment variable.                                    |
|                                                                          |
|       Syntax/   variable_name := oracle_home()                           |
|       Setup:                                                             |
|                                                                          |
|       Revision: None                                                     |
|                                                                          |
\*------------------------------------------------------------------------*/

#include <windows.h>
#include <process.h>
#include <stdlib.h>


__declspec(dllexport) char * oracle_home(
                            short       *ret_indicator)
{

   char *OracleHomeVar;
   OracleHomeVar = getenv("Oracle_Home");

   *ret_indicator = 0;
   return OracleHomeVar;


}

2)  Run oraclehome.sql.  NOTE:  As written this sql code uses an username of sample.  You should change or delete this as necessary.

oraclehome.sql

----------------------------------------------------------------------------
--                                                                        --
-- (c) Copyright 2003 Bertram Glenn Moshier - All Right Reserved          --
--                                                                        --
--    Program                                                             --
--      Name:     OracleHome.sql                                          --
--      Version:  1.00                                                    --
--      Date:     2003-02-24                                              --
--                                                                        --
--      Purpose:  Obtain the ORACLE_HOME environment variable and display --
--                it using an external C procedure.                       --
--                                                                        --
--      Syntax/   variable := ORACLE_HOME()                               --
--      Setup:                                                            --
--                                                                        --
--      Revision: None                                                    --
--                                                                        --
----------------------------------------------------------------------------

connect sample/sample;

drop library externProcedures;

create library externProcedures as 'c:\Oracle\Ora92\bin\oraclehome.dll';
/

CREATE OR REPLACE FUNCTION ORACLE_HOME
RETURN VARCHAR2 AS
   EXTERNAL LIBRARY externProcedures
   NAME "oracle_home"  -- Name of function call. Quotes preserve lower case.
   LANGUAGE C
   PARAMETERS (
      RETURN INDICATOR short ); -- need to pass pointer to return value's
/

show error

CREATE OR REPLACE PROCEDURE UseIt AS
oraclehome varchar2(256);
BEGIN
  oraclehome := ORACLE_HOME();
  dbms_output.put_line('Your Oracle_Home environment variable is: ' || oraclehome);
END;
/

show error

set serveroutput on
execute UseIt;
/
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8013950
P.S.  Two things I forgot to add:

1)  You need to move the DLL created by the Microsoft compiler to the %Oracle_Home%\bin subdirectory.  For example:  copy *.dll c:\oracle\ora92\bin\*

2)  You may have to enable the external listener extproc or it may already be on.  It was on for my database.

You can get more information on external C/Java/Visual Basic/etc., procedures on Oracle 9i Release 2 at:

http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a96590/adg11rtn.htm

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8013997
This is the readme.doc file on the Oracle 9i Release 2.  It has at least one glaring error.  "Step #3:  Run SVRMGRL ...," which no longer exists in Oracle 9i.

The file does give you information about extproc and other issues you might see on your Windows/2000 system.

Bert.



External Procedure Supplement

This document supplements the information in the Applications Development chapter of the Getting Started for Windows NT manual.  It explains how to run the sample and gives some trouble-shooting advice.

Running the Sample

1.  Install Microsoft Visual C++ 5.0.

2.  Run the MAKE.BAT file in the RDBMS\EXTPROC directory.  This creates the EXTERN.DLL.

3.  Run SVRMGRL and run the EXTERN.SQL script.  It prompts for the database's internal password.
      D:\ORANT\RDBMS\EXTPROC\>svrmgrl @extern.sql

This SQL script does the rest, including calling the PLSQL function "UseIt" that makes a call to the C function contained in the EXTERN.DLL.


Trouble-Shooting


PROBLEM:
You get the following error running the EXTERN.SQL script:
ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "SAMPLE.PLS_MAX", line 0
ORA-06512: at "SAMPLE.USEIT", line 8
ORA-06512: at line 2

SOLUTION:
This is most likely a SQL*Net configuration problem.  Make sure that your listener service is started.  Make sure that your TNSNAMES.ORA and LISTENER.ORA are properly updated.  If you make changes to your LISTENER.ORA, be sure to stop and re-start your listener.  The correct entry in your TNSNAMES.ORA is:
extproc_connection_data.world =
  (DESCRIPTION =
    (ADDRESS =
          (PROTOCOL = IPC)
          (KEY = ORCL)
    )
    (CONNECT_DATA = (SID = extproc)
    )
  )

The correct entry in your LISTENER.ORA should look like this:
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS=
          (PROTOCOL= IPC)
          (KEY= oracle.world)
        )
        (ADDRESS=
          (PROTOCOL= IPC)
          (KEY= ORCL)
        )
        (ADDRESS=
          (COMMUNITY= NMP.world)
          (PROTOCOL= NMP)
          (SERVER= YourServer)
          (PIPE= ORAPIPE)
        )
        (ADDRESS=
          (PROTOCOL= TCP)
          (Host= YourServer)
          (Port= 1521)
        )
        (ADDRESS=
          (PROTOCOL= TCP)
          (Host= YourServer)
          (Port= 1526)
        )
        (ADDRESS=
          (PROTOCOL= TCP)
          (Host= 127.0.0.1)
          (Port= 1521)
        )
        (ADDRESS=
          (PROTOCOL= SPX)
          (Service= YourServer_lsnr)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = 0
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = YourServer)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (SID_NAME = extproc)
      (PROGRAM=extproc)
    )
  )
PASSWORDS_LISTENER = (oracle)

PROBLEM:
When you build your DLL, you get the following missing exports:
ociepacm
ocieperr
ociepgoe
ociepmsg

SOLUTION:
Make sure you link with ORAPLS8.LIB in the ORANT\OCI\LIB\ directory.  You will also need to link with the ORACLIENT8.LIB as well.

PROBLEM:
When you execute your external procedure, you get an Application Error in EXTPROC.EXE.

SOLUTION:
Your function prototypes are not correct, you are returning invalid data, or you have messed up the stack in you function.  Try using DebugBreak(); to step into your DLL code to check the make sure you are not messing things up.

PROBLEM:
When you execute your external procedure, you get the following error
ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at "SAMPLE.PLS_MAX", line 0
ORA-06512: at "SAMPLE.USEIT", line 8
ORA-06512: at line 2

SOLUTION:
This is a time-out problem.  Perhaps while you were debugging your external function, you did not let your function return in time.  In that case it is harmless.  However, it could be an indication that you are taking too long in your external callout code.

PROBLEM:
You are having performance problems.

SOLUTION:
Performance is dramatically improved on future calls to external procedures in the same session.  So, try to make as many of your external procedure calls as possible before disconnecting.  Future releases will decrease this initial performance hit.

PROBLEM:
You want to limit the OS permissions of the DLL's that contain the external procedure.

SOLUTION:
Create a new listener service with its own LISTENER.ORA and have that service run under a user with limited permissions.

PROBLEM:
You are getting the following error when trying to execute the external procedure:
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Unable to load DLL
ORA-06512: at "SAMPLE.PLS_MAX", line 0
ORA-06512: at "SAMPLE.USEIT", line 8
ORA-06512: at line 2

SOLUTION:
Make sure that the full path name to your extern.dll is correct and that the DLL exists.
0
 
LVL 5

Author Comment

by:vachooho
ID: 8019947
This is quite good for Oracle 9 but the problem is that Oracle 8i doees not set Oracle_home environment variable (and for 9i there are 2 of them ORACLE_BASE and ORACLE_HOME).

It uses PATH variable for  determinig Oracle home directory. Getting startup path of running application, then loading some file (I dont remember the name) to determine registry entry and so on

0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8020518
The above code will work on Oracle 8.0.5, 8.1.X, 9.0.X, and 9.2.X.  You can change the environment variable name in the C program to say "PATH" and then parse it for the Oracle Home (e.g. \Oracle\Ora81).

I have code available to allow a PL/SQL program to request a specific environment variable (not always get Oracle_Home or require a recompile).

As for Oracle 9i Release 2, it does NOT set ORACLE_BASE just ORACLE_HOME.

Please note when Oracle 8, 8i, or 9i spins off an external procedure for PL/SQL it makes certain changes (e.g SID) and insure other environments are set (e.g. ORACLE_HOME), at least this has been my experience.

Did you try the above code on Oracle 8i?  I'm asking because the code that runs the external procedure should have set the ORACLE_HOME environment.
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8020604
FYI:  As for the environment variables ORACLE_BASE and ORACLE_HOME they are part of the "Oracle File Architecture."

Oracle_Base is the base subdirectory used by most (but not all) Oracle products.

Oracle_Home is the base subdirectory UNDER Oracle_Base used by an Oracle database.

This structure allows you (normally under Unix) to run multiple versions of the database and "keep straight" the Oracle products.

For example (using Unix):

Oracle_Base = /Oracle

Oracle_Home for 8i = $ORACLE_BASE/Ora81
Oracle_Home for 9i = $ORACLE_BASE/Ora92
Home for 9iAS = $ORACLE_BASE/iAS

While for Windows the example could show:

C:\Oracle => would be the "ORACLE_BASE" though the environment variable isn't set (at least for an external procedure)

C:\Oracle\Ora81 => is the Oracle_Home for Oracle 8i
C:\Oracle\Ora92 => is the Oracle_Home for Oracle 9i R2
C:\Oracle\iAS => is the home for the 9iAS server

---

In any case an external procedure on Oracle 8i is the way you'll need to go to obtain the ORACLE_HOME environment variable.  You can have the C routine parse the PATH statement and extract from it the directory.

Ah, You could also have the C routine ask/look through the registry.  It is there in the Oracle top level branch.
0
 
LVL 5

Author Comment

by:vachooho
ID: 8022038
>> Did you try the above code on Oracle 8i?  I'm asking
>>because the code that runs the external procedure should
>>have set the ORACLE_HOME environment.

Yes I run it and run it without ORACLE_HOME environment
true - it fails for getenv("ORACLE_HOME") but is able to return PATH and all other environment variables. I have no idea why it is failing (with RPC connection timeout) for the variable it can not find - but this is not what I want to know right now.

It seems that Oracle on Windows does not use ORACLE_HOME environment.

0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8030048
I hope my previous note and this information is enough to get you going in the correct direction.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

You should be able to modify the above C routine to examine the Windows Registry.  The ORACLE_HOME should be in there at:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME1


I also found the following from Oracle.  As I read it, you should on 8i (8.1.6 and above) be able to access the environment variables:  ORACLE_HOME, ORACLE_SID, and LD_LIBRARY_PATH


From:  Oracle8i Application Developer's Guide - Fundamentals Release 2 (8.1.6) Part Number A76939-01 chapter 10 "External Routines"

1. Set Up the Environment

Your DBA sets up the environment for calling external routines by adding entries to the files tnsname.ora and listener.ora and by starting a Listener process exclusively for external routines.

See Also:  Oracle8i Administrator's Guide.  

The Listener sets a few required environment variables (such as ORACLE_HOME, ORACLE_SID, and LD_LIBRARY_PATH) for extproc. Otherwise, it provides extproc with a "clean" environment.

And then later on:

Note:
It is possible for you to set and read environment variables themselves by using the standard C routines setenv() and getenv(), respectively. Environment variables, set this way, are specific to the extproc process, which means that they can be read by all functions executed in that process, but not by any other process running on the same machine.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0
 
LVL 7

Expert Comment

by:BobMc
ID: 8035447
I've never tried this...

Can you not read the values for all the Oracle homes from the registry - at least you know where they are all going to be i.e. HKLM\Software\Oracle

You might even be able to register the functions in D2KWUTIL.DLL as external procedures and read the values directly from PL/SQL.

HTH.
Bob
0
 
LVL 5

Author Comment

by:vachooho
ID: 8036322
Registry works when only 1 home installed on the computer
When you add second home - default_home value not changed in the registry and it always points to the first added oracle home.

as for D2KWUTIL.DLL - I never use it and can not find neither in Ora\BIN nor in Win32 folders. Could you provide more infor on this
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8051697
Here's an idea.  Create a file combining the attributes of:

1)  oratab (file containing ORACLE_SID, ORACLE_HOME using by Unix systems to support multiple Oracle homes, SIDs, or environments)

2)  Oracle environment scripts used on Unix systems.

This file would contain the basic information you desire for each Oracle SID (e.g. SID, Oracle_Home) like oratab.  Since it is of your creation for your own use, you can add stuff beyond ORACLE_SID and ORACLE_HOME - just like the Unix envrionment scripts.  You could even use symbolic symbols (e.g. %ORACLE_HOME%\bin), if you desire.  Using UTL_FILE (which you're already using), have the PL/SQL program access:

1)  This new file
2)  The Oracle SID (v$database)

Using the above two items, you should be able to obtain any information you desire about an instance, since you control what goes into this new file.  The PL/SQL program could also resolve the symbolic symbols, of your choosing.

Of course, this isn't a simple drop-in place and it works solution.  You have to create the "oratab/information" file, but this could simply be part of the installation procedures.

At least it seems to meet your requirements for an Oracle 8i system without getting into the registry via a C program.  I thought about it when looking at the registry program.

Each Oracle Home has a "group" entry (e.g. HOME0, HOME1, HOME2), even the iAS has one of these entries (no ORACLE_SID, though).  One of the entries in these group entires is the ORACLE_SID, when the group is a database.

I was thinking EITHER of having the PL/SQL program pass the instance name OR have the external procedure obtain the instance name itself.  The external procedure would take this information to locate which of these "group" entries represent the ORACLE_SID the PL/SQL procedure is using and contains the necessary ORACLE_HOME information.

A light came on saying, why use an external procedure at all!  (As Scotty on Star Trek says, "Use the right tool for the right job.)  Why not have the PL/SQL procedure you're writing access a file with the information you desire in the same way I was going to have the external procedure access the registry.

If you still want an external routine to obtain the ORACLE_HOME (and other registry stuff) via the registry, you should be able to find it in the registry groups (shown above in my previous messages).  You would do this by matching the group with the Oracle_SID provided by v$database.
0
 
LVL 7

Expert Comment

by:BobMc
ID: 8058773
default_home will always point to the first installed home, so if you have more than one home, scan HOME0, HOME1, HOME2 up to the value in ...\ALL_HOMES\HOME_COUNTER -1

Each of these has an ORACLE_HOME and an ORACLE_HOME_NAME key, so assuming you give your homes sensible names, it should be straight forward to determine which one is being used where.

Alternatively, look in HKLM\SYSTEM\CurrentControlSet\Services\... for the OracleServices, which will (usually) return the path to the bin directory.


You could either write an external procedure to read the appropriate bits of the registry, or if you just need to ORACLE keys, use D2KWUTIL.DLL

D2KWUTIL is a developer library used from Forms, but there is no reason why you can't reference its functions externally. It comes with Oracle Forms&Reports (and possibly 9iAS). Alternatively, you can download it from Oracle (if its still there...)

ftp://oracle-ftp.oracle.com/dev_tools/patchsets/dev2k/Win95NT/d2kwutil/
D2KWUTIL.PLL is a comprehensive utility containing many of functions for the
Win32 environment that users ask for, which are not included in the Oracle
Developer Product set by default The utility itself consists simply of a
PL/SQL library (D2KWUTIL.PLL) and a Windows DLL (D2KWUT60.DLL).
The utility uses the ORA_FFI package to interact with it's DLL and then this
DLL calls the various Windows API functions and some internal code to carry
out the requested action.

HTH
Bob
0
 
LVL 4

Expert Comment

by:MathiasMagnusson
ID: 9152700
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
[Accept bmosier's (first) comment as answer.]
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

MathiasMagnusson
EE Cleanup Volunteer
0
 
LVL 5

Author Comment

by:vachooho
ID: 9161559
no answer was received -
still no way to determine ORACLE_HOME
0
 
LVL 4

Expert Comment

by:MathiasMagnusson
ID: 9164375
I think bmosier gave you more than 100 ppoints worth of suggestions. Did you even try the external proc and then use the getenv() to get the value?

0
 
LVL 5

Author Comment

by:vachooho
ID: 9175099
YES -  I TRIED THAT GETENV() DOES NOT RETURN ORACLE_HOME IF IT IS NOT DEFINED INSYSTEM ENVIRONMENT MANUALY. ORACLE DOES NOT SET THIS VARIABLE IN WINDOWS.
I WOULD BE GLAD TO ACCEPT ANY ANSWER WHICH WILL GIVE ME AT LEAST AN IDEA HOW TO FIND ORACLE_HOME
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 9177978
Then you have done something incorrectly because I have it working with "GetEnv()" on  both Oracle 8i (817) and 9i (902).
0
 
LVL 4

Expert Comment

by:MathiasMagnusson
ID: 9178042
Could it be that you somehow manages to start the instance without setting the ORACLE_HOME? If so, couldn't you fix it by making sure it's set before the instance is brought up? Either way, a moderator will come by and make a decision soon. Unless there is enough evidence by then that some collaboration is going on, the question will be closed.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question