Connect Oracle Database Memos with Navicat Premium and pl\sql developer in 64-bit Win7

Recently took over a project where the server-side database is Oracle 11g 64-bit. Because the main job is not development, and do not want to install a large Oracle database on their own computers, it is thought that only through the database management tools to connect the database for some common query operations.

Operating Environment

Server: Windows Server 2008 64-bit

Server Oracle Version: Oracle 11g R2 64-bit, character set is ZHS16GBK.

Native: Windows 7 64-bit

Software that needs to be prepared

1, Instant Client 12.1.0.2.0 32-bit and 64-bit

The Instant client is a streamlined version of the customer's website, launched by Oracle Corporation.

The latest version is 12.1.0.2.0, which downloads the 32-bit and 64-bit versions of basic respectively. The basic release contains all the files required to run the OCI, OCCI, and JDBC-OCI applications.

As for why the 32-bit and 64-bit versions of the instant Client are downloaded separately, they are detailed later.

2, Navicat Premium 11.1.10 64-bit

Navicat Premium is a set of database management tools that can be easily connected to MySQL, MariaDB, SQL Server, SQLite, Oracle, and PostgreSQL databases in a single program.

The official website is: Http://www.navicat.com.cn/products/navicat-premium.

Since this machine is a 64-bit operating system, download the latest 11.1.10 64-bit version.

3, Pl\sql Developer 10.0.5.1710

PL/SQL Developer is an integrated development environment designed specifically for the development of Oracle Database storage program units. PL/SQL developer focuses on ease of use, code quality, and productivity, giving full play to the key benefits of Oracle application development. The official website is http://www.allroundautomations.com/plsqldev.html, the download version of this article is 10.0.5.1710, the official also provides the Simplified Chinese language pack.

Give me the feeling that Navicat premium is more convenient, while the database, Pl\sql developer more professional, Oracle's development management more powerful. So select two software are installed.

Installation configuration

1, decompression instant Client

The 32-bit and 64-bit instant clients are extracted and renamed, and the native decompression and renaming paths are as follows:

32-bit: D:\Databases\Oracle\instantclient_12_1_x32

64-bit: D:\Databases\Oracle\instantclient_12_1_x64

2. Configuration of Navicat Premium OCI

In fact, Navicat Premium comes with instantclient_10_2, but it is a lite version of Base Lite (Basic Lite:basic, with only English error messages and Unicode, ASCII and Western European character set support), the Chinese character set is not supported, and the server-side Oracle in this article happens to be the Chinese character set, instantclient_10_2 is not appropriate, so you need to download the base version.

Set the OCI library in Navicat Premium to D:\Databases\Oracle\instantclient_12_1_x64\oci.dll, and then reboot to take effect.

3. In navicat Premium configuration database connection

There are two types of connection for Oracle in NAVICAT Premium: Basic and TNS.

Basic

In Basic mode, Oracle is connected through the Oracle call Interface (OCI). OCI is an application interface that allows program developers to access the Oracle database and control the stages in which all SQL statements run by using the third-generation language legacy process or function call. OCI is a standard database access library and a dynamic link library form retrieval function.

Basic mode requires a host name or IP address, port, and service name or SID.

Tns
In TNS mode, you need to use an alias item in the Tnsnames.ora file to connect to an Oracle server through Oracle call Interface (OCI).

The TNS mode only needs to select the network service name, but before you need to create a new Tnsnames.ora and configure the database service.

It is recommended to use a more concise basic mode, which eliminates complex tnsnames configurations. The basic model is used in this article.

In the "New Connection" interface, enter the connection name, connection type, hostname or IP address, port, service name or SID, username and password, and click OK to connect to the database.

4, configure the OCI of pl\sql Developer

In the Preferences-oracle-connection interface, configure the Oracle home directory name and the OCI library.

Oracle Home directory Name: D:\Databases\Oracle\instantclient_12_1_x32

OCI Library: D:\Databases\Oracle\instantclient_12_1_x32\oci.dll

5. Configure database connection in Pl\sql developer

The Pl\sql developer can also be configured with the basic and TNS connection modes.

The main difference between the two is that the database entry, the basic mode needs to enter the "hostname or ip/service name" format data connection string, TNS mode can be selected in the drop-down box in the configured database.

As with Navicat, it is recommended to use a more concise basic mode, while setting up Pl\sql developer to keep the login history is much easier.

6, solve pl\sql developer Query Chinese table appears question mark garbled problem

In Pl\sql developer, querying a table that contains Chinese characters will become a segment question mark, mainly because the server is not the same as the client's character set.

Execute the following SQL statement to view the server database character set:

SELECT * from V$nls_parameters

The main parameters are:

Nls_laguage:simplified Chinese
Nls_territory:china
Nls_characterset:zhs16gbk

So in this machine we need to configure the following environment to ensure the consistency of the server and native character sets.

Variable name: Nls_lang

Variable value: Simplified Chinese_china. Zhs16gbk

Check the Chinese table again, we will find that Chinese is normal again.

A picture to outline

At this point, using the client Navicat Premium and Pl\sql developer through the Instant Client Connection server Oralce the installation configuration process for the database has been completed.

Spit Groove

In fact, the installation of the configuration process is not as easy as expected, but we have to hold the "nothing to do, things are not afraid of the matter" attitude to solve the problems encountered. The main problems with the spat are the following:

Issue 1:navicat report ora-12737:instant Client light:unsupported server character Set ZHS16GBK

The problem is caused by the direct use of Navicat-instantclient_10_2.

Comes with a basic lite version of the instantclient.

Lite version of Basic Lite:basic with only English error messages and Unicode, ASCII, and Western European character set support

Basic: All the files required to run the OCI, OCCI, and JDBC-OCI applications, including the Chinese character set.

The solution is to change the basic version of Instantclient.

Issue 2:navicat report cannot load OCI DLL, 193

You can click Http://wiki.navicat.com/wiki/index.php/Instant_client_required to view the official solution.

But formally looked at the official solution, so that I went around a good big curved to connect the database properly.

Navicat version 10 or below, Navicat only supports 32-bit instant client.

And I ignored the first sentence, understood as Navicat only support 32-bit instant client, download 32-bit instant client, but Navicat version 11, note we use Navicat version 11,navicat version 11 The 64-bit version does not support 32-bit instant client, only supports 64-bit instant client, and most of the articles on the web are NAVICAT version 10 using the 32-bit Instant Client connection database, So I have always suspected that the decompression of the wrong, repeated trial and error, wasted a lot of time to wake up.

Connect Oracle Database Memos with Navicat Premium and pl\sql developer in 64-bit Win7