• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6651
  • Last Modified:

Default Oracle Client Version

I have oracle client 9i, and 10g installed on my system

I am trying to connect to oracle 8i using SQLPlus on 9i. It works fine. I know that oracle 10g client does not support 8i database. Hence I use 9i client to connect to 8i server

When I try connect to oracle 8i server from outside applications (for eg mapguide's mapauthor) it always sees oracle 10g's home and tries to find out all tns names in its tnsnames.ora i.e. 10g's tnsnames.ora. Hence it cannot find tnsnames defined to connect to version 8?

How can I make all applications outside oracle to use oracle 9i as client and not 10g as default client?

Pl help.
0
at999
Asked:
at999
  • 11
  • 8
  • 7
  • +1
3 Solutions
 
sathyagiriCommented:
Make your default home as Oracle 8i.

You can do this by editing your reegistry use regedit.
0
 
at999Author Commented:
I have registry key folder oracle_homes. in that i have a subfolder oracle0. In oracle0 there is registry key - oracle_home which has the value : "C:\orant"
I dont knwo why it is pointing to this folder!!!

Then I have ORACLE_HOME_NAME which has value of "DEFAULT_HOME"

Which  registry key shud i modify?
What value shud I assign? I have oracle servers installed in other folder and not in c:\orant

do i have to start stop any service after changing the registry key?
0
 
sathyagiriCommented:
Under ORACLE_HOMES you would have some thing called default_home ,
set that to OracleClient8i and under ORACLE0 set the name to OracleClient8i and path to the 8i install base folder.

Please make sure you have a backup of your original values , just in case the above doesn't work.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
at999Author Commented:
I have following keys set

Key Name:          HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOMES
Value 0
  Name:            DEFAULT_HOME
  Type:            REG_SZ
  Data:            OracleClient9i


Key Name:          HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOMES\ORACLE0
Value 0
  Name:            ORACLE_HOME
  Type:            REG_EXPAND_SZ
  Data:            D:\9i_oracle

Value 1
  Name:            ORACLE_HOME_NAME
  Type:            REG_EXPAND_SZ
  Data:            OracleClient9i

Value 2
  Name:            SHARED_ORACLE_HOME
  Type:            REG_EXPAND_SZ
  Data:            D:\9i_oracle

Value 3
  Name:            API
  Type:            REG_EXPAND_SZ
  Data:            D:\9i_oracle\DBS

Value 6
  Name:            ORACLE_GROUP_NAME
  Type:            REG_EXPAND_SZ
  Data:            Oracle for Windows NT

Value 7
  Name:            OracleClient9i
  Type:            REG_SZ
  Data:            D:\9i_oracle


it does not work!!
do i need to restart machine or any service?
0
 
actonwangCommented:
>>How can I make all applications outside oracle to use oracle 9i as client and not 10g as default client?

    What you need to in order to make outside applicatoin to use oracle 9i is to use registry editor to change       \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME to <oracle9i directory>.

     I should be used by third party application to find the oracle home.
0
 
actonwangCommented:
>>HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOMES
    it should be used by univisal installer, don't touch it.

>> HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOMES\ORACLE0
    those entries are used by  oracle 9i itself, not by applications. don't touch it as well!

go to  \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
     if there is value named <ORACLE_HOME>, try to change it to "D:\9i_oracle" to make applications to use oralce 9i.
0
 
schwertnerCommented:
Go to Programs ---> Oraracle Installation Products and try to find "Home selector"
It can switch the default home.
If you do not find it
alternativelly use OUI:
           start -->
              All programs -->
                    Oracle - Ora10g_Home1 -->
                         Oracle Installation Products -->
                                Launch the Universal Installer -->
                                      Click on "Installed Products: button
                                           Click on "Environment" tab
                                                  Move your Ora10g_Home1 to the top using the arrows on the right side.
                                                       Click on "Apply" button
0
 
actonwangCommented:
>>it always sees oracle 10g's home and tries to find out all tns names in its tnsnames.ora
     check on <ORACLE_HOME> , I guess that it points to oracle 10g's home.
0
 
at999Author Commented:
I checked none of the registry keys point to oracle 10g's home

When I do a tnsping to any SID, it always uses tnsping utility of oracle 10g. !!!  and never of 9i

schwertner,
I changed the oraclehome using Universal installer, but it does not work.

Actually i am trying to connect coldfusion server to oracle using an - odbc link!! it does not work!!

0
 
sathyagiriCommented:
Does your coldfusion server reside on the same box as your oracle server or is it a separate box?
0
 
actonwangCommented:
>>When I do a tnsping to any SID, it always uses tnsping utility of oracle 10g. !!!  and never of 9i

     That is because the default path set it to oracle 10g bin home.

     If you go to D:\9i_oracle\bin , then issue "tnsping" you will be able to use 9i tnsnames.
0
 
actonwangCommented:
did you see or try to change ORACLE_HOME as I told you?
0
 
actonwangCommented:
>>If you go to D:\9i_oracle\bin , then issue "tnsping" you will be able to use 9i tnsnames.
    you could change your PATH  environment to put D:\9i_oracle\bin in it and remove 10g bin directory. then by default, you will use 9i utilities including sqlplus and tnsping.
0
 
at999Author Commented:
Ya  after changing the path variable i can figure out what client version oracle will use!!

Actually its the problem with coldfusion server, it cannot find the instance names !!!

I am trying to debug that
0
 
actonwangCommented:
good. also try to set ORACLE_HOME in the registry to see how it goes.
0
 
at999Author Commented:
sathyagiri
>>Does your coldfusion server reside on the same box as your oracle server or is it a separate box?
No coldfusion Server is on my machine. Oracle client(Version 9 and 10) is on my machine. Oracle Server(Version 8) is on other machine
0
 
sathyagiriCommented:
Ok try this modify your system path environment variable to make sure the 9i\bin comes before the 10g\bin and try invoking tnsping or sqlplus
0
 
actonwangCommented:
he already did as I told.
0
 
sathyagiriCommented:
Try renaming the tnsnames.ora under your 10G network/admin folder to something else and see if it picks up the 9i tnsnames.ora
0
 
at999Author Commented:

Thank you all for your help.

Oracle OLEDB also looks for Driver in oracle 10g!!!
Eventhough I have oracle9i added in path, oracle9i set as oracle_home, it does not work.
0
 
at999Author Commented:
I tried to create an oledb connection, using oracle for oledb driver. It gives error that
OraOLEDBpus10.dll The specified module could not be found.
0
 
sathyagiriCommented:
Make sure the path variable includes 9i\bin before 10G\bin

(ie)
It should be something like

PATH=D:\Oracle9i\Bin;D:\Oracle10G\Bin
0
 
at999Author Commented:
I assigned proper paths - to the bin folder in the path env variable.

When I try to create an oledb link using oracle oledb provider it gives error -
Test connection failed because of an error in initializing provider. Catastrophic failure

I can connect to the same database using microsoft oledb provider
Also the ODBC Connection works!!!

So what is causing this confusion of version for oracle provider and for external application - coldfusion.!!!
0
 
schwertnerCommented:
Instead using tnsnames.ora (if you really use it!) in coldfusion try to use direct value.
This means to put in the connection string instead the alias from the tnsnames.ora
the string of that entry.
0
 
at999Author Commented:
coldfusion uses an odbc oracle connection !!! Which does exists and I have tested that connection using control panel's administrator.

But it does not connect thorugh the coldfusion applicaiton !
0
 
sathyagiriCommented:
When setting up your ODBC connection from control panel instead of using TNS alias , specify it using a direct value per
schwertner's suggestion.
Something like
"(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521)))(CONNECT_DATA =(SID = SERVERSTUFF)(SERVER = DEDICATED)))"
0
 
at999Author Commented:
It does not  allow such a long name for TNS Alias/name
"DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521)))(CONNECT_DATA =(SID = SERVERSTUFF)(SERVER = DEDICATED)))"

Hence odbc connection does not work if i put such long name.
0
 
at999Author Commented:
I did all the changes possible to coldfusion code -

<cfquery name="Prop" datasource="saturn" dbtype="odbc" >
i changed dbtype to oledb and others but none of them worked!!!

Is this problem with coldfusion?

I can easily connect to any version of oracle using any client using java based application !!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 8
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now