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.
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.
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?
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.
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.
ASKER
I have following keys set
Key Name: HKEY_LOCAL_MACHINE\SOFTWAR E\ORACLE\O RACLE_HOME S
Value 0
Name: DEFAULT_HOME
Type: REG_SZ
Data: OracleClient9i
Key Name: HKEY_LOCAL_MACHINE\SOFTWAR E\ORACLE\O RACLE_HOME S\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?
Key Name: HKEY_LOCAL_MACHINE\SOFTWAR
Value 0
Name: DEFAULT_HOME
Type: REG_SZ
Data: OracleClient9i
Key Name: HKEY_LOCAL_MACHINE\SOFTWAR
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?
>>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\SOFTWA RE\ORACLE\ ORACLE_HOM E to <oracle9i directory>.
I should be used by third party application to find the oracle home.
What you need to in order to make outside applicatoin to use oracle 9i is to use registry editor to change \HKEY_LOCAL_MACHINE\SOFTWA
I should be used by third party application to find the oracle home.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
check on <ORACLE_HOME> , I guess that it points to oracle 10g's home.
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!!
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.
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.
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.
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
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.
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
>>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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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.
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:\Or acle10G\Bi n
(ie)
It should be something like
PATH=D:\Oracle9i\Bin;D:\Or
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.!!!
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.
This means to put in the connection string instead the alias from the tnsnames.ora
the string of that entry.
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 !
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)))"
schwertner's suggestion.
Something like
"(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521)))(CONNECT_DATA =(SID = SERVERSTUFF)(SERVER = DEDICATED)))"
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.
"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.
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 !!!
<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 !!!
You can do this by editing your reegistry use regedit.