I have installed an oracle client in my machine and at the same time the machine is having an express edition database installed .My question is how can i make both the clients working ? The one which is installed later sets the path variable. Despite changing the value of path variable , i am unable to connect through the client .A detailed reply would be really great .
This is not a PL/SQL or SQL language question and therefore not on-topic for this forum.
Oracle XE is not a client. It is a server.
Client-server is a software architecture. This means client and server can, and often do, co-exist on the same h/w platform.
For the Oracle client (from a different ORACLE_HOME installation) to connect it needs to establish a TCP (SQL*Net) connection with the server. The type of connection is typically specified via a TNS connection string.
For the Oracle server to accept the TCP connection, it needs to have an Oracle Listener servicing it. The Listener accepts TCP connections and hands these off to the database server.
And this is the basics. The fact that both are on the same h/w platform, or different h/w platforms, does not change the SQL*Net client-server basics that need to be in place.
I agree to all what you have written .But as you said we often have client server co-existing on the same hardware platform and also i have additionally installed an oracle client .I agree that the TCP connections is via Net8 (Sql*Net) /ODBC etc. But my question is i want to connect to both my XE server and my other database servers (Production) using a single client (since m having 2 clients , one that came along with the XE database and the other which i have explicitly installed) ,I get the following error
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory .Despite setting the path and ORACLE_HOME environment variables , the error persists.
Oracle XE comes with client libraries. This requires you to set ORACLE_HOME (for client IPC connections in addition to TCP), and the PATH to enable the client to load the OCI client driver (DLL on Windows).
A pure client install comes into two flavours. The everything-and-kitchen-sink client. The instant client (preferred).
In this case you only need to set PATH - as only TCP connections are possible and not IPC. Thus no need to set ORACLE_HOME in order for the OCI client driver to spawn a dedicated Oracle server process for itself.
Multiple clients can co-exist. But only a single client can be used at a time - and I suspect this is system wide. The OCI DLL is not version named AFAIK, which means 2 clients (both 32bit for example) will have a OCI DLL filename collission. The kernel will load this DLL only once from a specific client path - and this DLL will be used by all OCI clients.
I suspect that both these environments are mixed (via your o/s session's environmental variables) - thus the errors.
I suggest keeping it simple - and keeping client driver sets for the same database totally separate and selecting to only use a single one.
Why have you installed multiple clients? What problem are you trying to resolve?