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.
Server: Windows Server 2008 64-bit
Server Oracle Version: Oracle 11g R2 64-bit, character set is ZHS16GBK.
Native: Windows 7 64-bit
1, Instant Client 220.127.116.11.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 18.104.22.168.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.
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:
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.
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.
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:
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.
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.
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.
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