Working with Code after Installing an Oracle PL/SQL Database - dummies

Working with Code after Installing an Oracle PL/SQL Database

By Michael Rosenblum, Paul Dorsey

After you have the database installed, the next question is, “Where will you run all these PL/SQL programs?” The answer isn’t as simple as you might think. The ultimate goal is productivity, and you can create PL/SQL programs from numerous tools.


If you’ve already been working with Oracle, you’ve probably heard of SQL*Plus. This tool is installed with all versions of Oracle (both server and client). The main purpose of SQL*Plus is to provide quick and easy access to the database in both interactive and batch modes. Compared to the leading GUI development tools, SQL*Plus is a relatively primitive tool that most professional developers rarely use.

You can run SQL scripts, PL/SQL scripts, and other scripts by using the internal command language of SQL*Plus by itself. Many developers do all their work by using SQL*Plus because they don’t trust the fancy GUI tools.

Several versions of SQL*Plus are available after completing the installation of the Oracle database:

  • The command line interface is the most widely used tool because it can be called from any operating system batch language.
  • The basic SQL*Plus GUI tool consists of a wrapper around the same command line interface. It has some GUI features such as the ability to set options in the special form, but it can hardly be called very user friendly.
  • iSQL*Plus is the latest one and has some nice features, but it also might require some workarounds to deal with timeout settings and security patches.

Because SQL*Plus is included with all Oracle configurations, it remains extremely popular despite its relatively minimal capabilities. For more details about SQL*Plus, you can find a lot of documentation on OTN as well as many custom scripts. It isn’t necessary to know a lot about SQL*Plus, but understanding some basics about how to run/save/edit scripts could be useful in case you’re limited to SQL*Plus.

Oracle SQL Developer

In the first part of 2006, Oracle added a new tool to the mix — Oracle SQL Developer (formerly known as Project Raptor). It’s a free Java-based graphical environment targeted at database developers. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements.

Currently, this product it still in the “Early Adopter” phase, although it is a good alternative to the command line interface. The authors do not recommend using it in production systems without significant testing, but SQL Developer looks very promising. If you don’t have access to a more mature third-party tool, try it (click here

Third-party tools

In addition to SQL*Plus and newly born Oracle SQL Developer, a number of other tools enable you to work with PL/SQL. The reason for using any one of these is to make your life easier. Of course, any GUI might have bugs/restrictions/issues, but overall the pros usually outweigh the cons. Here are a few popular options:

  • Toad from Quest Software: Toad is the most popular PL/SQL coding tool on the market. It includes a nice editing environment for SQL and PL/SQL. However, the most distinctive feature of Toad is a very powerful set of administrative tools. Toad provides a full working environment for DBAs and power users.
  • SQL Navigator from Quest Software: SQL Navigator, another product from Quest Software, has a more limited audience. It is built by Oracle developers for Oracle developers. Everything there is optimized for writing PL/SQL or SQL as quickly and effectively as possible. It isn’t as useful for DBAs, but its add-ons and overall functionality make it a very attractive option for server-side developers.
  • RapidSQL from Embarcadero: RapidSQL, another major development-centered tool, is focused on working with the whole IT environment. Because Embarcadero targets more enterprise-wide solutions, RapidSQL has many features that might just get in the way and be confusing for the PL/SQL beginner. However, RapidSQL does include everything you need to maximize your productivity.

For more information, you can read a set of reviews of these tools here