Link to home
Start Free TrialLog in
Avatar of at999
at999

asked on

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.
Avatar of sathyagiri
sathyagiri
Flag of United States of America image

Make your default home as Oracle 8i.

You can do this by editing your reegistry use regedit.
Avatar of at999
at999

ASKER

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?
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.
Avatar of at999

ASKER

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?
Avatar of Acton Wang
>>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.
SOLUTION
Avatar of Acton Wang
Acton Wang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>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.
Avatar of at999

ASKER

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!!

Does your coldfusion server reside on the same box as your oracle server or is it a separate box?
>>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.
did you see or try to change ORACLE_HOME as I told you?
>>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.
Avatar of at999

ASKER

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
good. also try to set ORACLE_HOME in the registry to see how it goes.
Avatar of at999

ASKER

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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
he already did as I told.
Try renaming the tnsnames.ora under your 10G network/admin folder to something else and see if it picks up the 9i tnsnames.ora
Avatar of at999

ASKER


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.
Avatar of at999

ASKER

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.
Make sure the path variable includes 9i\bin before 10G\bin

(ie)
It should be something like

PATH=D:\Oracle9i\Bin;D:\Oracle10G\Bin
Avatar of at999

ASKER

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.!!!
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.
Avatar of at999

ASKER

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 !
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)))"
Avatar of at999

ASKER

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.
Avatar of at999

ASKER

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 !!!