SQL*Plus Goodies
Store and restore session state
It is possible to store the current session state to a file, and restore it later:
SQL> STORE SET mysession.sql REPLACE
SQL> …
SQL> @mysession.sql
The STORE SET <filename> creates a SQL file which can be simply executed
anytime to restore the session state.
Error logging
Starting with Oracle 11g it is possible to have all occuring errors logged to a
database table. This way they are even available for lookup when the SQL*Plus
window already has been closed. Plus, of course, one can filter errors using
the WHERE clause.
To turn on this feature inside a SQL*Plus session, simply issue the statement
SET ERRORLOGGING ON [ TABLE <tabname>]
As indicated, a special logging table can be passed. Default is sperrorlog.
This target table must have the following columns:
username VARCHAR2(256)
timestamp TIMESTAMP
script VARCHAR2(1024)
identifier VARCHAR(256)
message CLOB
statement CLOB
Please do not ask me what caused Oracle to a) use a deprecated type (VARCHAR) and b) a reserved word (TIMESTAMP) for a column name with this new feature …
There are two more optional parameters available to the SET ERRORLOGGING command:
TRUNCATE start with a clean table (truncate it first)
IDENTIFIER <id> value for the `identifier` column, for filtering