What is SQL*Plus?

SQL*Plus is a programming environment for writing SQL and PL/SQL code. You can use SQL*Plus to:

Run interactively in a window
Run in the background to execute predefined scripts
Execute just about any SQL command, including commands to create users, tables, tablespaces, and indexes
Adjust a long list of environment settings to produce formatted reports that include headers, footers, page breaks, and calculated summaries
Debug SQL and PL/SQL scripts by executing them, thus gathering instant feedback on the syntax

As a Database Administrator, I use SQL*Plus to generate customized SQL scripts to grant table privileges and roles to users. I also use SQL*Plus to generate Web pages that are refreshed periodically by re-running the SQL*Plus script. My favorite accomplishment is the SQL*Plus script I created that generates a complex replication trigger for any table in my database.

Set errorlogging

SQL*Plus has added the set errorlogging command to provide additional methods of trapping errors. When enabled, set errorlogging will cause errors generated by SQL, PL/SQL, and SQL*Plus to be written to a logging table (SPERRORLOG by default) for you to review. Oracle will create the SPERRORLOG table if it is not present when you enable error logging. Here is an example of the use of the set errorlogging command:

-- enable error logging
SQL> set errorlogging on
-- bad query

SQL> select * from dud;
ORA-00942: table or view does not exist
-- Here is a desc of the sperrorlog that the error messages are written to.

SQL> desc sperrorlog
Name Null? Type
----------------------------------------- -------- --------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT VARCHAR2(1024)
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
-- Query the sperrorlog table.
SQL> select username, timestamp, statement, message from sperrorlog;
USERNAME TIMESTAMP STATEMENT
---------- ------------------------------ -----------------------------

MESSAGE
---------------------------------------
SYS 11-AUG-07 09.54.47.000000 PM select * from dud
ORA-00942: table or view does not exist