Installing and configuring PL/SQL Developer

1- Introduction

This document is based on:
  • PL SQL Developer 10.x

  • ORACLE 11g

2- Some visual tools working with Oracle database

While  SQLServer or  MySQL has a download package, in which after you install it, a visual tool is available, after installing Oracle, you need to install another additional visual tool, but it has a variety of choices for you. Based on my working experience, I think that   PL/SQL Developer is the best tool. However, we still look at some other choices:

2.1- Oracle SQL Developer

Oracle also provides an  Oracle SQL Developer visual tool for free, and you can download it at:  

2.2- Toad

Toad is commercial software, and a visual tool working with Oracle. It is highly appreciated.

2.3- PL/SQL Developer

This is commercial software. Compared with above software, it is obviously the best one.

3- Models of Oracle connection

  When you install one of visual tools working with Oracle, the model is similar to the following image:
  • Situation 1: The visual tool on your computer is connected with database Oracle on another computer. You need to install Oracle Client or an  Oracle Database on your computer.
    • Note:  Oracle Database plays the role of both a server and an  Oracle Client
  • Situation 2: The visual tool on your computer is connected with database Oracle on the same computer. Then this database plays the role of both an Oracle Server and an Oracle Client. You needn't install anything else (There is some troubles with Oracle 64bit).

4- There is a trouble with PL/SQL Developer

There is a trouble with the visual tool, including  PL/SQL Developer:
This trouble happens in the situation that visual tool connecte with  Oracle Database 64bit on the same computer.

SQL Navigator, Toad, Oracle SQL Developer, PL/SQL Developer raise error OCI.dll when connect to  Oracle 11g 64bit.

Cause of error:
In that case, you must overcome it by installing  Oracle Client  32bit. (Note: It must be 32bit). Simultaneously, the version of   Oracle Client must be similar to that of  Oracle Database that has been installed on the computer.

5- Download PL/SQL Developer

Download PL/SQL Developer at: Currently the latest version is 10.x

6- Installing PL/SQL Developer

7- Configuring PL/SQL Developer to connect to Oracle Server

We split some cases to configure:
  1. PL/SQL Developer connect to Oracle Database 32bit on the same computer.
  2. PL/SQL Developer connect to Oracle Database 64bit on the same computer.
  3. PL/SQL Developer connect to Oracle Database on another computer (your computer has installed Oracle)
  4. PL/SQL Developer connect to Oracle Database on another computer (your computer does not install Oracle).

7.1- Configuring PL/SQL Developer connect to Oracle Database 32bit on the same computer

This is the simplest case. Just run the PL/SQL Developer, enter the username, password, SID. Everything is OK.

7.2- Configuring PL/SQL Developer connect to Oracle Database 64bit on the same computer

This is the model for PL/SQL Developer connect to Oracle Database 64-bit on the same computer:
Open PL/SQL Developer:
Click OK and get an error message:
Do not worry about it. Click OK to return. Then click Cancel.
PL/SQL Developer opened, but you can't use.
You need to configure the location of  OCI.DLL 32bit file. First, you download  Oracle Client 32bit (Note: It must be 32bit). You can see the below Appendix:
This is the Oracle Client folder you unzipped.
Enter the path to the file oci.dll, located in the directory of the Oracle Client.
Click OK. And rerun PL/SQL Developer:
And you're ready to work with PL/SQL Developer.
As usual, you only need  the configuration as instructed above that you can connect to Oracle 64bit from PL/SQL Developer. In case you still get the error message:
In this case, you can treat your computer as another remote  computer, you install the Oracle Client on your computer and connect to the Oracle of that remote computer. You can view the following case:
  • Configuring PL/SQL Developer connect to Oracle Database on another computer (your computer does not install Oracle)
In case you receive an error MSVCR100.dll causes Oracle Client version is not the same version of Oracle Database version you have installed.

7.3- Configuring PL/SQL Developer connect to Oracle Database on another computer (your computer has installed Oracle)

This is a connection model between  PL/SQL Developer and  Oracle Database on another computer. On your computer  Oracle Database is available.
OR:
Suppose   PL/SQL Developer has been connected with  Oracle Database on the same computer. Next, we configure in order to connect with  Oracle Database on another computer, in this case, you need have the configuration on  tnsnames.ora file:
In the case of Oracle 11g tnsnames.ora file located in the directory:
  • <ORACLE_INSTALL_DIRECTORY>/product/11.2.0/dbhome_1/NETWORK/ADMIN
Content of file tnsnames.ora:
Add the following code into the file TNSNAME:
DB11G_ABC =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = host_abc)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = SID_ABC)
   )
 )
Trong đó:
  • SID_ABC : SID of the Oracle Database is located on the computer you want to connect.
  • host_abc : Is the computer name or IP address of the computer you want to connect.
  • DB11G_ABC : TNSNAME (Alias)
 
After configuration is complete, now connect to the Oracle Database Server:

7.4- Configuring PL/SQL Developer connect to Oracle Database on another computer (your computer does not install Oracle)

In case your computer does not install Oracle Database. You need to download the 32bit Oracle client:
  • Configuring OCI library path for PL/SQL Developer.
  •  Create a file named tnsnames.ora and configured on this file
Add subfolders:
  • NETWORK/ADMIN
Add 2 files sqlnet.ora & tnsnames.ora
  • sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)

 
  • tnsnames.ora
DB11G_SERVER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora_server_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db11g)
    )
  )
Configure environment variable Path:
Now you can use PL/SQL Developer:

8- Oracle PL/SQL Programming Using PL/SQL Developer

After Installation and configuration PL/SQL Developer success. You can view additional - Oracle PL/SQL Programming Tutorial Using PL/SQL Developer at:

9- Appendix: Install Oracle Client

There are many versions of Oracle Client, you see:
  • Version 12.1.0.2.0
  • Version 12.1.0.1.0
  • Version 11.2.0.4.0
  • Version 11.2.0.3.0
  • Version 11.2.0.2.0
  • Version 11.2.0.1.0
You need to download the correct, same Oracle version currently installed on your computer. You should have a few checks.

Note: Must be correct version, otherwise will not run.

Enter:
  • system/<password>@<sid>
You will know which version of Oracle you have installed
In my case, I use Oracle 11g 64bit version 11.2.0.1.0, so I need to download the 32bit version 11.2.0.1.0 Oracle Client.

Note: Oracle requires username/password to download, you need to register an account oracle (It's free).

Unzip the downloaded file into a certain location on the hard drive.