Use Navicat Premium and PL \ SQL Developer in 64-bit Win7 to connect to the Oracle database memo, win7navicat

Use Navicat Premium and PL \ SQL Developer in 64-bit Win7 to connect to the Oracle database memo, win7navicat

Recently, I took over a project where the server database is oracle 11g 64-bit. Because it is not a development task and you do not want to install a large oracle database on your computer, you can only use database management tools to connect to the database for some common query operations.

Running Environment

Server: 64-bit windows server 2008

Server oracle version: oracle 11g R2 64-bit, character set is ZHS16GBK.

Local: windows 7 64-bit

Software to be prepared

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

Instant Client is a lite Client launched by Oracle.

The latest version is 12.1.0.2.0. In this article, the Basic versions of 32-bit and 64-bit are downloaded. Basic contains all the files required to run OCI, OCCI, and JDBC-OCI applications.

As to why the 32-bit and 64-bit Basic versions of Instant Client are downloaded separately, we will introduce them in detail later.

2. Navicat Premium 11.1.10 64-bit

Navicat Premium is a set of database management tools that can be connected to MySQL, MariaDB, SQL Server, SQLite, Oracle, and PostgreSQL databases in a single program at the same time, which is very convenient.

Official Website: http://www.navicat.com.cn/products/navicat-premium.

Because the local operating system is 64-bit, download the latest version 11.1.10 64-bit.

3. PL \ SQL Developer 10.0.5.1710

PL/SQL Developer is an integrated development environment dedicated to the development of Oracle database storage program units. PL/SQL Developer focuses on ease of use, code quality, and productivity, and gives full play to the main advantages of Oracle application development. The official website is elastic.

What I feel is that Navicat Premium is more convenient and database; PL \ SQL Developer is more professional, and oracle development and management is more powerful. Therefore, install both software.

Install configurations

1. decompress the Instant Client

Decompress and rename the 32-bit and 64-bit Instant clients respectively. The local decompression and rename paths are as follows:

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

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

2. Configure Navicat Premium OCI

In fact, Navicat Premium comes with instantclient_10_2 by default, but it is a base lite version (Basic Lite: A simplified version of Basic, which only contains English error messages and Unicode, ASCII, and western European character set support ), the Chinese character set is not supported, while the server-side oracle in this article happens to be a Chinese character set. instantclient_10_2 is not suitable, 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 restart and take effect.

3. Configure database connection in Navicat Premium

In Navicat Premium, oracle has two connection types: Basic and TNS.

Basic

In Basic mode, connect to Oracle through Oracle Call Interface (OCI. OCI is an application interface that allows program developers to access the Oracle database and control the running of all SQL statements using the original process or function call in the third-generation language. OCI is a standard database access library and a function that is searched in the form of a dynamic link library.

In Basic mode, you must enter the host name, IP address, port, and service name or SID.
TNS
In TNS mode, you need to use the alias project in the tnsnames. ora file to connect to the Oracle server through Oracle Call Interface (OCI.

In TNS mode, you only need to select the network service name, but you need to create tnsnames. ora and configure the database service.

We recommend that you use a simpler Basic mode to save complicated tnsnames configurations.This document uses the Basic mode.

On the "New Connection" Page, enter the connection name, connection type, host name or IP address, port, service name or SID, user name, and password, and click "OK" to connect to the database.

4. ConfigurationPL \ SQL DeveloperOCI

On the "preferences-Oracle-connection" Page, configure the Oracle main directory name and OCI library.

Oracle main directory name: D: \ Databases \ Oracle \ instantclient_12_1_x32

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

5.PL \ SQL Developer configure database connection

 

The Basic and TNS connection modes can also be used for configuration in PL \ SQL Developer.

The main difference between the two lies in the database input items. In Basic mode, you need to enter a data connection string in the format of "host name or IP/service name". In TNS mode, you can select the configured database from the drop-down box.

Like Navicat, we recommend that you use a simpler Basic mode and set PL \ SQL Developer to keep logon history, which is much easier.

6,SolutionThe question mark garbled problem occurs when PL \ SQL Developer queries a Chinese table.

In PL \ SQL Developer, when querying a table containing Chinese characters, Chinese characters are converted into question marks in the limit segment, mainly because the character sets of server and client are different.

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

SELECT * FROM v$nls_parameters

The result is as follows:

Http://jingyan.baidu.com/article/a3aad71aa9bfefb1fa00964d.html

Http://1249479040qq.iteye.com/blog/1991367

Http://www.cnblogs.com/xwdreamer/p/3649993.html

Http://www.educity.cn/wenda/583545.html

Http://blog.csdn.net/jbgtwang/article/details/7702897

Http://wiki.navicat.com/zh-chs/index.php/%E9%9C%80%E8%A6%81_Instant_client