Oracle SQL*Plus: The Definitive Guide

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 the demo tables that are installed with every version of Oracle. 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.”

This is still arguably the major reason most people use SQL*Plus today, over fifteen 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. System R was the product of a research effort by IBM. Some of IBM’s documents referred to the command-line interface as the User Friendly Interface, and that name was adopted by Oracle for their interactive SQL utility.

One of the more interesting uses Oracle had for UFI was as a tool to produce their documentation. The 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 that 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. SQL*Plus still plays a significant role in Oracle’s testing, even today.

SQL*Plus maintains a fascinating relic from the old days in the form of the SET TRIMOUT, SET TRIMSPOOL, and SET 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 a dial-up speed of 1200 bps was fast. 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 LAN connections and our 56kb modems, we hardly give this a thought.

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 the Vice-President of Data Server Product Management for Oracle. In spite of the public demos, whether or not to actually release AUFI as a shipping product was the subject of some debate within Oracle. Trying to layer a procedural language on top of the existing UFI command set was proving very difficult. It was made even more difficult by the need to maintain full backward compatibility so that 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 very complex and somewhat unreliable. The issues of reliability and complexity led to Oracle’s ultimate decision to kill the product, so AUFI never actually 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 product was changed to SQL*Plus. The changes since then have been mostly evolutionary. Each new release brings with it a few new commands and a few 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.

The most recent changes to SQL*Plus have been the addition of support for commands such as STARTUP, SHUTDOWN, and RECOVER, which were previously only available in Server Manager. SQL*Plus is now the primary command-line interface to the Oracle database. Even SQL Worksheet, Enterprise Manager’s ad-hoc query tool, makes use of SQL*Plus to execute the commands that you enter. Oracle’s future plans for the product don’t call for anything revolutionary, just a steady stream of enhancements in order to keep up with each new release of Oracle. It’s just possible that SQL*Plus will be around for another fifteen years.