What is Oracle Client?

This is software layer providing access to Oracle Database. Some applications have integrated Oracle Client, some require it to be separately installed by admin. Integrated (you may be interested if you are developer) called Instant Client, as admin you don’t care about it.

Oracle Client is required for software, that doesn’t integrate instant client, including Devart dotConnect and Microsoft Connector for Oracle by Attunity.

Where to download Oracle Client?

At www.oracle.com from Downloads → Databases → Oracle Database, where you have to select version of DBMS you work with or higher.

Taking client of higher version will be plus, because you will not have to upgrade it in production, when you have to connect to another instance of database (can turn out to be higher).

Don’t mess up Oracle Client and Oracle Instant Client, intended for developers. Also, don’t download Data Access Components, because DAC, besides Oracle Client, contains a lot of tools, needed only for application development.

Installation of Oracle Client 12c 32-bit doesn’t go after installing of Oracle Client 12c 64-bit (or otherwise)

If you have just installed one of the Oracle 12c clients and had no reboot, just reboot.

Installer of Oracle Client, named Oracle Universal Installer (crazy big piece of software) creates service OracleRemExecService that, conforming to unofficial description, is needed only for OUI and should disappear after reboot. In fact it doesn’t disappear, but also doesn’t start after reboot (which is good). Is it correct to just stop service i don’t know, but it also helps.

How to configure connection to Oracle database in application, that uses Oracle Client?

From the side of application, that uses Oracle Client, to specify database there are two ways — put name of TNS service or configuration string, containing part of definition of TNS.

In first case inside of Oracle Client folder in the file \network\admin\tnsnames.ora you write:

connection name =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = )(Port = ))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = )
 )
)

In that case, in applications in field TNS Service Name you write this connection name.

You should know, that in application, running in 32-bit environment, Oracle Client 32-bit is used, but in 64-bit — Oracle Client 64-bit, therefore it may be needed to create tnsnames.ora in both clients.

What is SERVICE_NAME and SID?

Connection to database over a network at server side is served by middleware, named Listener.

SID is unique identifier of Oracle database at machine, while SERVICE_NAME is identifier of database, configured in Listener. The same database can be accessed under different SERVICE_NAME, but have only one SID. Because you are outside of database machine, you care of SERVICE_NAME.

How to address tables in Oracle?

By default, user of Oracle calls tables in his schema. To write queries that use the same tables despite credentials they run under, you need to use schema name before dot. For instance, use BM9.BM_CUSTOMER_CONTACT to address table BM_CUSTOMER_CONTACT in BM9 schema.

What is schema and database in Oracle?

Database in Oracle = separated pool of DBMS processes with shared memory.

Schema is contained within database and is container for tables. And schema correspond to user.

Tables with the same names can exist in different schemas simultaneously.

Can not detect OCI environment (for instance in Attunity). What’s wrong?

An application that uses Oracle Client should somehow find it. Installation path of Oracle Client is added to %PATH% by Oracle Installer at a moment of install. But you should know, that environment variables are set for processes at a moment of their start. That is why, for instance, Visual Studio (BIDS, Data Tools), started before Oracle Client installation, require to be restarted to use new %PATH%.

This error can be differently phrased in different applications.

How to configure codepage for Oracle Client?

Incorrectly configured codepage can be an issue for non-English strings. It often arise for Russian, because we have number of coding pages for the same Cyrillic letters. This happens in form of differently working, for instance, REPLACE(table_column, 'А', 'Б') — may work in one tool and not work in another, because literals ‘А’ and ‘Б’ are differently interpreted by one of the tools.

For 32-bit Oracle Client, in registry at [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE] set "NLS_LANG"="RUSSIAN_CIS.CL8MSWIN1251" (of type REG_SZ).

For 64-bit Oracle Client in registry at [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE], similarly, set "NLS_LANG"="RUSSIAN_CIS.CL8MSWIN1251" (of type REG_SZ).

After configuration you have to reboot, because already started Oracle Client will not read it.