topic Re: Specify Oracle Client Version in Database Connection in Data Management Questions

Specify Oracle Client Version in Database Connection

JoshuaBixby — Wed, 03 Aug 2011 21:10:49 GMT

Thus far, after checking a couple of different Help file locations, I am unable to figure out how to specify the Oracle client version in a Database Connection when a machine has multiple versions of Oracle clients installed. Prior to 10.1, the Oracle client version was easily specified in the connection parameters.

Re: Specify Oracle Client Version in Database Connection

TomBrown — Fri, 05 Aug 2011 03:14:34 GMT

I'm curious, how would you have done this prior to 10.1?

ArcGIS simply makes the call to the Oracle client which would be the first one in the library path as the DLL is loaded.

Thanks.

Re: Specify Oracle Client Version in Database Connection

JoshuaBixby — Fri, 05 Aug 2011 14:10:31 GMT

It is common in our organization for GIS staff, and occasionally other GIS users, to have 2 versions of Oracle client on their machines: Oracle 9i and Oracle 11g. The need for 2 Oracle clients is driven by non-GIS reasons; however, depending on which SDE instance people connect to, the direct connect strings vary. For example, the two most common direct connects are:

     SDE:ORACLE9i:LEGACYINSTANCE
     SDE:ORACLE11G:INSTANCE

Since the LEGACYINSTANCE has SDE 9.2 SP6, Oracle 11g client cannot be used to connect from ArcGIS Desktop 9.3/9.3.1.

It could very well be that both connections are using the Oracle 11g client since it is the first Oracle client in the system's path. I guess that is a question for Esri. If that is the case, i.e., both use the same Oracle client, then my original question should have been:

   How does one specify different Oracle direct connect libraries in the Database Connection?

Re: Specify Oracle Client Version in Database Connection

EugeneYang — Fri, 05 Aug 2011 15:29:49 GMT

ArcGIS 10.1 can automatically figure out which direct connect driver to use, 10g or 11g, depending on which Oracle client is installed.

Oracle9i is no longer supported and ArcGIS 10.1 can not connect to 9.2 geodatabase on Oracle9i. I don't think you can have both ArcGIS 9.3.x and ArcGIS 10.1 on the same machine. Therefore, the particular situation you described would not happen.

If you absolutely need to have both Oracle11g and Oracle9i clients on the same machine, just make sure that Oracle11g is the first in the PATH on the ArcGIS 10.1 machine, and Oracle9i cleint is the first in the PATH on the ArcGIS 9.3.x machine.

Re: Specify Oracle Client Version in Database Connection

JoshuaBixby — Fri, 05 Aug 2011 16:16:58 GMT

The example in my earlier post isn't hypothetical, it exists for ArcGIS 9.3.1. I realize ArcGIS 10.1 won't connect to SDE 9.2; my question was really about the larger issue of specifying Oracle clients or libraries. If Esri has added code for the database connection to " automatically figure out which direct connect driver to use", then that is what I needed to know because I could not find documentation that explicitly stated that information.

Prior to ArcGIS 10.1, in the not-so-distant past, the user had to specify different clients or libraries on one machine. Automatic is good, as long as it works....

Re: Specify Oracle Client Version in Database Connection

JoshuaBixby — Fri, 10 Dec 2021 22:03:36 GMT

ArcGIS simply makes the call to the Oracle client which would be the first one in the library path as the DLL is loaded.

I haven't had a chance to test with ArcGIS 10.0 or 10.1, but I just don't believe the situation is so with ArcGIS 9.3/9.3.1. On our organization's machines that are configured with Oracle 10g and 11g clients and ArcGIS 9.3.1, it seems ArcGIS is looking for specific Oracle clients and not just the first Oracle client in the path.

The behavior on our machines is:

[INDENT]System PATH           SDE direct connect string    Result
10g path;11g path  sde:oracle10g                    Connection Succeeded
10g path;11g path  sde:oracle11g                    Connection Succeeded
11g path;10g path  sde:oracle10g                    Connection Succeeded
11g path;10g path  sde:oracle11g                    Connection Succeeded
10g path                  sde:oracle10g                    Connection Succeeded
10g path                  sde:oracle11g                    Failed:  Server library could not be loaded
11g path                  sde:oracle10g                    Failed:  Server library could not be loaded
11g path                  sde:oracle11g                    Connection Succeeded[/INDENT]

If ArcGIS was simply looking for the first Oracle client in the system path, then errors would be generated if the SDE direct connect string called a different Oracle version than the first one in the system path.

Re: Specify Oracle Client Version in Database Connection

JoshuaBixby — Tue, 16 Aug 2011 18:24:04 GMT

The previous table of results for 9.3.1 is identical for our machines with ArcGIS 10.0 installed on them.

Re: Specify Oracle Client Version in Database Connection

JoshuaBixby — Wed, 17 Aug 2011 14:37:28 GMT

The behavior must have changed for 10.1. I have a machine with Oracle 10g and Oracle 11g clients installed, and both clients are fully functional. When I try to make a connection to SDE 9.2, I get the following error:[INDENT]Database Connection
Failed to connect to the specified server. Do you wish to continue?
ArcGIS 9.2 geodatabases do not support direct connect using Oracle 11g clients. Please use the Oracle 10g client or the application server.
[/INDENT]Ignoring the fact that 10.1 connections to 9.2 are not supported anymore, how does one use the Oracle 10g client if there is no place to specify an Oracle client version when making a connection?

Re: Specify Oracle Client Version in Database Connection

JoshuaBixby — Thu, 18 Aug 2011 13:55:05 GMT

Seeing that the Oracle client choice is supposed to be automatic at 10.1, and the error message I received is the red herring discussed in another thread, I guess it is likely ArcCatalog tried both Oracle clients (10g and 11g), failed with both clients (SDE 9.2 isn't supported anymore), and then only presented one error message to the end user.

If there is one suggestion to come out of this experience for me, it would be that more descriptive error messages would help clear the fog for end-users and speed troubleshooting for Esri Support.

Re: Specify Oracle Client Version in Database Connection

EugeneYang — Thu, 18 Aug 2011 18:05:13 GMT

The error message you saw when connecting to a 9.2 geodatabase is misleading. It is actually a known bug. We are fixing this issue at 10.1, and it will return a simple error that this release of geodatabase is no longer supported.

Thanks.

Re: Specify Oracle Client Version in Database Connection

TomBrown — Thu, 18 Aug 2011 19:34:17 GMT

I guess it is likely ArcCatalog tried both Oracle clients (10g and 11g)

Esri doesn't attempt to use any Oracle client... ArcGIS is compiled with Oracle's libraries. When we make a call to Oracle's Call Interface (OCI) it loads the Oracle client library. The library it loads is the first client it finds in the path.

Esri has no control over what Oracle client is loaded.

Some of the confusion in this exchange is in regards to the "direct connect" libraries... these are Esri libraries. In previous releases you had to specify in the direct connect syntax the Esir client library to use with Oracle - either Oracle 10g or 11g (and 9i when it was supported). Based on what was specified in the connection string, ArcGIS would load the Esri library which was compiled using the specific Oracle release. It's this library which then attempts to establish the connection to the database by calling the OCI function to create a connection (which in turn loads it's dependencies - the installed Oracle client).

What we've accomplished with ArcGIS 10.1 is to simplify the connection experience by now requiring the user to only specify which DBMS platform they are connecting to... for example when the user selects Oracle we only have 1 Esri library used to make the connection (which is based on Oracle 11g libraries) and again - its the Oracle library which pulls in its own client libraries.

Hopefully this clearly explain the architecture of what happens during a connection and the clear distinction of Esri's libraries and Oracle's libraries.

If you have dependency's on your machines to have both an Oracle 9i or 11g client then you need to speak with Oracle support on how to configure this properly for multi applications.

Good luck and thank you for posting.

Re: Specify Oracle Client Version in Database Connection

JoshuaBixby — Fri, 19 Aug 2011 20:27:22 GMT

Tom, thanks for the more in-depth explanation of what occurs during a connection and what changed in 10.1. I was generally aware of the differences between Oracle client libraries and Esri direct connect libraries, but I wasn't sure of how they interacted, especially in 10.1. My questioning and frustration were in earnest; I was trying to understand what changed in 10.1 and why I was seeing certain behaviors.

Your statement, "we only have 1 Esri library used to make the connection (which is based on Oracle 11g libraries)," is the most informative for me, and it will be good to communicate the Oracle 11g part of that statement to our organizations IT architects.