Solved

Default Oracle Client Version

Posted on 2006-07-07
28
6,598 Views
Last Modified: 2009-08-19
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
Comment
Question by:at999
  • 11
  • 8
  • 7
  • +1
28 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17058203
Make your default home as Oracle 8i.

You can do this by editing your reegistry use regedit.
0
 

Author Comment

by:at999
ID: 17058266
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17058299
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
 

Author Comment

by:at999
ID: 17058446
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17058537
>>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
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 50 total points
ID: 17058571
>>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
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 50 total points
ID: 17058581
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17058582
>>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
 

Author Comment

by:at999
ID: 17058892
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17058928
Does your coldfusion server reside on the same box as your oracle server or is it a separate box?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17058929
>>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
 
LVL 19

Expert Comment

by:actonwang
ID: 17058939
did you see or try to change ORACLE_HOME as I told you?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17058969
>>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
 

Author Comment

by:at999
ID: 17059598
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:actonwang
ID: 17059640
good. also try to set ORACLE_HOME in the registry to see how it goes.
0
 

Author Comment

by:at999
ID: 17060936
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
 
LVL 14

Accepted Solution

by:
sathyagiri earned 150 total points
ID: 17062718
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
 
LVL 19

Expert Comment

by:actonwang
ID: 17063391
he already did as I told.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17063582
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
 

Author Comment

by:at999
ID: 17092895

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
 

Author Comment

by:at999
ID: 17092919
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17092929
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
 

Author Comment

by:at999
ID: 17094035
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
 
LVL 47

Expert Comment

by:schwertner
ID: 17097168
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
 

Author Comment

by:at999
ID: 17101436
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
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17101645
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
 

Author Comment

by:at999
ID: 17101743
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
 

Author Comment

by:at999
ID: 17101779
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now