Oracle SQL*Plus: The Definitive Guide, 2nd Edition

History of SQL*Plus

SQL*Plus has been around for a long time, pretty much since the beginning of Oracle. In fact, the original author was Bruce Scott. Any DBA will recognize the name Scott. It lives on, immortalized as the owner of a set of example tables that used to be installed with every version of Oracle and that you still can install even today. The original purpose of SQL*Plus can be summed up in the succinct words of Kirk Bradley, another early author of SQL*Plus, who told me, "We needed a way to enter statements into the database and get results."

Kirk's reason is still arguably the major reason most people use SQL*Plus today, more than 15 years after it was originally written. SQL*Plus certainly satisfies a compelling and enduring need.

The original name of the product was not SQL*Plus. The original name was UFI, which stands for User Friendly Interface. This name has its roots in one of the first relational database systems ever developed, IBM's System R, the product of a research effort by IBM. Some of IBM's documents referred to the command-line interface as the UFI, and that name was adopted by Oracle for its interactive SQL utility.

One of the more interesting uses Oracle had for UFI was as a tool to produce its documentation. The SQL*Plus DOCUMENT command, now considered obsolete, was used for this purpose. Script files were created that contained the manual text, interspersed with the SQL statements needed for the examples. The DOCUMENT command was used to set off the manual text so it would just be copied to the output file. When these scripts were run, the text was copied, the SQL statements were executed, and the result was documentation complete with examples.

Tip

UFI was used extensively in Oracle's internal testing and QA efforts. Even today, SQL*Plus still plays a significant role in Oracle's testing.

SQL*Plus maintains a fascinating relic from the old days in the form of the SET TRIMOUT, SET TRIMSPOOL, and S ET TAB commands. These commands control the printing of trailing spaces and the use of tabs to format columnar output. To understand why these commands even exist, you have to realize that when SQL*Plus first made its appearance, people thought that a dialup speed of 1200 bits per second (bps) was fast. In those days you could get your results much faster by avoiding the need to transmit large numbers of space characters across a dialup connection.

If you had a lot of whitespace in your report, you spent a lot of time watching spaces print across your screen. In that environment, trimming spaces and using tabs to format columns provided a huge gain in throughput. Today, with our 10-megabit-per-second (10 Mbps) LAN connections and our 56-KB modems, we hardly give this a thought.

Tip

While TRIMSPOOL can be considered a relic from the standpoint of interacting with SQL*Plus via an interactive session, it's helpful to trim trailing spaces from the kinds of large files you might create when using SQL*Plus to extract data, as described in Chapter 9. Example 9-8 uses TRIMSPOOL for just this reason.

During the mid-1980s, Oracle experimented with efforts to add procedural capabilities to UFI. The result of this effort was AUFI, which stood for Advanced User Friendly Interface. AUFI implemented such things as IF statements and looping constructs, and was demonstrated publicly at an International Oracle User Group meeting in 1986 by Ken Jacobs, who is now Oracle's Vice President, Product Strategy, Oracle Server Technologies.

In spite of the public demos, whether or not to release AUFI as a shipping product was the subject of some debate within Oracle. Layering a procedural language on top of the existing UFI command set was proving difficult. It was made more difficult by the need to maintain full, backward compatibility so existing scripts written by Oracle's clients would not suddenly break when those clients upgraded. Because of these issues, the code to support the procedural enhancements became complex and somewhat unreliable. The issues of reliability and complexity led to Oracle's ultimate decision to kill the product, so AUFI never shipped. With the later advent of PL/SQL, procedural logic was supported within the database, and efforts to support a procedural scripting language were then seen as unnecessary. The name AUFI lives on in the name of the temporary file created when you use the SQL*Plus EDIT command. That file is named afiedt.buf. Even today, AFI is the prefix used for all the source code.

With the release of Oracle 5.0 in 1985, the name of the interactive query utility was changed from UFI to SQL*Plus. Most changes since then have been evolutionary. Each new release brings with it a few new commands and new options on existing commands. Some commands have been made obsolete, but many of these obsolete commands are still supported for purposes of backward compatibility.

Only a couple of truly significant changes to SQL*Plus have occurred over the years. In 1988, with the release of Oracle8i Database, Server Manager's STARTUP, SHUTDOWN, and RECOVER commands were implemented in SQL*Plus, which was designated the primary, command-line interface into the Oracle database. Server Manager was deprecated, and by the time Oracle9i Database came along, Server Manager no longer existed.

By far the most significant and visible change to SQL*Plus in recent years has been the introduction of iSQL*Plus, a three-tier application that gives you access to SQL*Plus functionality via any standard web browser.

As a prelude to iSQL*Plus, it was first necessary to give SQL*Plus the ability to generate output in HTML form. This took place in Release 8.1.7, which introduced the SET MARKUP command. Chapter 6 describes this wonderful, new functionality, which you can use to generate HTML reports from command-line SQL*Plus.

iSQL*Plus was first released in 2001 as a Windows-only application (part of Oracle9i Database Release 1). iSQL*Plus was expanded to most other operating systems in 2002 with the release of Oracle9i Database Release 2. Beginning with the release of Oracle Database 10g, iSQL*Plus is supported across all platforms.