SQL Utilities -
SQL*Plus
SQL*Plus is a basic yet very
critical command line utility. It is very
reminiscent of TTY console, applications from
days past. All the key interaction with SQL*Plus
is via the keyboard and typed commands. While
that may seem a bit quaint, most senior DBAs and
senior developers prefer scripting using command
line utilities like SQL*Plus. In fact, most have
built up libraries or a collection of such
scripts that they take with them from job to
job. Figure 5.2 showcases what a basic SQL*Plus
session and interaction looks like.
Figure
5.2:
SQL*Plus
command line utility
There
are several key things to note about SQL*Plus.
First, it supports all the basic ANSI SQL
commands plus some SQL*Plus specific extensions.
Some of those proprietary extensions support
interaction with the command line editor, e.g.
append, change, del, get, input, list and save. In fact,
SQL*Plus editing works very much like the old
DOS Edlin program. Other extensions support
database management related tasks like connect,
disconnect, shutdown, startup and password. Some
extensions support scripting (accept, define,
pause, prompt, undefine and variable). Finally,
a large number of commands support numerous data
formatting and reporting options such as break,
btitle, column, compute, repfooter, repheader,
set and
tttile.
It is
almost certain that SQL*Plus will be a valuable
tool for all of the DBA’s Oracle database
activities. It is hard to imagine a scenario
where there would not be a need for SQL*Plus on
a weekly, if not daily, basis because it is
simply that overwhelmingly
useful.
Note: The Windows specific
version of SQL*Plus (SQL*PLUSW) was a more
graphical oriented application resembling
SQL*Plus. However, it was discontinued. So
only the simple SQL*Plus command line
utility remains.
SQL Developer
For
many years there was no lasting standard
graphical user interface tool for working with
Oracle databases. At one point, Oracle
introduced Procedure Builder, a great little
tool for PL/SQL developers, but it went away.
Then Oracle on Windows offered Database Manager,
a little lightweight OEM-like utility for
managing databases, and it too disappeared. As
for Oracle Enterprise Manager, it started out as
a fat-Java client tool, but then became a
web-based application. In addition, Application
Express (APEX) is very useful for APEX work, but
it is not suitable as a general-purpose Oracle
database tool. Therefore, many people relied on
tools like
Toad® for years as a decent GUI for general
purpose Oracle work.
Fortunately, Oracle finally saw the need for
such a tool and built a very good freeware
offering called SQL Developer. SQL Developer can
be downloaded from their website as a standalone
freeware offering and, starting with Oracle 11g,
it is now part of the standard Oracle client
install. Figure 5.3 shows what SQL Developer
looks like. There are far too many features in
SQL Developer to do it justice in just a brief
section of one chapter of a book. Nevertheless,
there are numerous blogs, discussion forums and
even entire books now on the product, so search
the web for information and the latest details.
Note that as a Java based application, SQL
Developer runs on numerous platforms and not
just Microsoft Windows. So for UNIX, LINUX and
even MAC-OS users, SQL Developer may well be the
tool of choice.
It is good to observe that while SQL
Developer does increasingly support SQL*Plus
command syntax, it is not an entire replacement
for it.
Figure 5.
3:
SQL
Developer
