Installing and configuring PL/SQL Developer

1- Introduction

This document is based on:

• PL SQL Developer 10.x

• ORACLE 11g,12c

2- Some visual tools to working with Oracle

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:  

• http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

2.2- Toad

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

• http://www.quest.com/toad-for-oracle/

2.3- PL/SQL Developer

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

• http://www.allroundautomations.com/plsqldev.html

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 Oracle Server and an Oracle Client

• Situation 2: The visual tool on your computer is connected with Oracle DB 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 (But, 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 DB 64bit on the same computer.

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

Cause of error:

In that case, you must overcome it by installing  Oracle Client  32bit. (Note: It must be 32bit).

See more:

• Installing Oracle Client on Windows

5- Download PL/SQL Developer

Currently the latest version of PL/SQL Developer is 10.x, and you can download it at:

• http://www.allroundautomations.com/plsqldev.html

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 DB 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 DB 64bit on the same computer

This is the model for PL/SQL Developer connect to Oracle Database 64-bit on the same computer:

Make sure you have the Oracle Client installed.

• Installing Oracle Client on Windows

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 file 32bit. Make sure you have installed the 32bit Oracle Client.

This is the Oracle Client folder, that you have installed.

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.

7.3- Configuring PL/SQL Developer connect to Oracle DB 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:

Install Oracle Client if necessary:

• Installing Oracle Client on Windows

Assuming that you have successfully connected PL/SQL Developer to Oracle DB on the same computer. To connect to Oracle DB on another computer you need to configure the tnsnames.ora file:

In the case of Oracle 11g tnsnames.ora file located in the directory:

• /product/11.2.0/dbhome_1/NETWORK/ADMIN

Content of file tnsnames.ora:

Add the following code into the tnsnames.ora file:

tnsnames.ora

DB11G_ABC = (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = host_abc)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = SID_ABC)    ) )

• 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 DB on another computer (your computer does not install Oracle)

In case your computer does not install Oracle Database. You need to install the 32bit Oracle client:

• Configuring OCI library path for PL/SQL Developer.
•  Create a file named tnsnames.ora and configured on this file

Make sure you have the Oracle Client installed.

• Installing Oracle Client on Windows

Add subfolders in the Oracle Client root directory:

• /network/admin

Add 2 files sqlnet.ora & tnsnames.ora (If they do not exist)

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:

Path=D:\DevPrograms\oracleclient_12.2.0\bin;......

Now you can use PL/SQL Developer: