Direct Connect Parameter Stored as Environmental Variable

[MichaelVolz]

To All ArcSDE Users:

My organization is in the process of upgrading the Oracle database from 10g to 11g.  As a result the Direct Connection information will need to be changed on tens of thousands of connections within our mxds, because the Service parameter needs to be changed from sde:oracle10g to sde:oracle11g.

In order to avoid this vast amount of work in the future, I was wondering if this information can be stored as an Environmental Variable that would be read in when the mxd is opened up.  In this way, when my organization upgrades the Oracle database in the future, only the Environmental Variable would need to be modified.

Any help or hints are greatly appreciated.  Thanks in advance.

[VinceAngelo]

Environment variables are honored by the SE_connection_create function, but not by the ArcGIS GUI. 
In any case, it is probably unwise to put authentication information in a user's environment, allowing
any application run on the host to misappropriate it.

There are scripts available to rewrite connection information, though this is a lot less effort than
reinstalling Oracle on that many hosts.

- V

[JoelBennett]

Note that what you have in the service parameter is based upon the version of Oracle Client you have on the client machine, not the database version on the server.  So if you upgrade the database to 11, but leave the 10 client on your client machines, you could still use sde:oracle10g.  You would only have to change if you remove the Oracle 10 client from your client machines.

[MichaelVolz]

Joel:

My organization should be able to leave the Oracle10g connection information intact if we upgrade the Oracle server to 11g, but leave the clients at 10g?

Is there new functionality of Oracle11g that would not be available if we leave Oracle Client at 10g on the client machines?

Is the Oracle10g Client compatible with Oracle 12g, so we would be able to leave the connection information as is with an upgrade of Oracle Server to 12g?

Your help and feedback is greatly appreciated.  Thanks.

[JoelBennett]

Before answering these questions, I just have to say I haven't tried using any of the configurations you've mentioned, but would be fairly confident it would work.  We're currently using Oracle database 10.2.0.4, but I have both Oracle 10 and Oracle 11 clients on my machine, and I can use either version of the client and still get the same results for the things we do.

For question number 1, yes, but only because you use the word "should".

For question number 2, yes the 11 client has new features, but you could probably still get by with the 10 client.

For question number 3, probably...

Nothing can substitute for your own testing, since you know best what you and your company do with this software.  You can probably find documentation from Oracle about new features for each release, etc, which would be worth your while to look into.

[MichaelVolz]

Joel:

You wrote

"We're currently using Oracle database 10.2.0.4, but I have both Oracle 10 and Oracle 11 clients on my machine, and I can use either version of the client and still get the same results for the things we do."

When you are connecting to Oracle database 10.2.0.4 thru the Oracle 11 client, you are not able to access new Oracle 11 functionality, correct?

The only way to get Oracle 11 functionality is to have both the database and client upgraded to the 11 version, correct?

[JoelBennett]

Answer to both is no.  For example, support for Oracle User defined types is new in the Oracle 11 Client's Oracle Data Provider for .NET.  I've used this functionality for extracting SDO_GEOMETRY information from our Oracle 10 database, and it works fine.