Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

1.2 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.

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
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
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.

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
Oracle8 i 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 Oracle9 i 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
i SQL*Plus,
a three-tier application that gives you access to SQL*Plus
functionality via any standard web browser.

As a prelude to i SQL*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.

i SQL*Plus was first released in 2001 as a
Windows-only application (part of Oracle9 i
Database Release 1).

i SQL*Plus was expanded to
most other operating systems in 2002 with the release of
Oracle9 i Database Release 2. Beginning with the
release of Oracle Database 10 g ,
i SQL*Plus is supported across all
platforms.