The Oracle SQL*Plus product has introduced
some new features that have long been missing from this utility
program, and some to support the new features. Here’s a rundown of
the new features in Oracle SQL*Plus.

GLOGIN.SQL, LOGIN.SQL, and SQLPROMPT

Previously, the glogin.sql and login.sql files
were executed only once during login to SQL*Plus. In Oracle 10g,
the glogin.sql and login.sql are run whenever you connect to a new
user. This is useful because you may want to change settings
depending on the user.

Also, the SQLPROMPT command is automatically
re-evaluated when you connect to a different user. This is useful
if you want to change a user’s SQL> prompt to show the user and
instance name. For example:

SQL> set sqlprompt
“_user’@’_connect_identifier> “
SCOTT@orcl> connect sys/admin as sysdba
Connected.
SYS@orcl>

DBMS_OUTPUT inside SQL SELECT

The DBMS_OUTPUT enhancement helps you debug
functions used in SELECT statements. Pre-SQL*Plug 10g,
DBMS_OUTPUT.PUT_LINE from within a function wouldn’t return output
if you ran the function from within a SQL SELECT statement. The
workaround was to issue an exec dbms_output.put_line(‘x’); after
the SELECT statement to flush out the debug output. Now, this is
done for you automatically:

create or replace function foo return
varchar2
as
begin
    dbms_output.put_line(‘–>
begin’);
    null;
    dbms_output.put_line(‘<– end’);
    return ‘foo’;
end;
/
show errors;

SQL> set serveroutput on
SQL> select foo from dual;

FOO
—
foo

–> begin

<– end

Recycle Bin

The new Oracle Flashback Drop feature allows
you to recover tables that are accidentally dropped. It shows
tables that have been dropped and are available for flashback. Listing A shows an example.

SPOOL APPEND, CREATE, and REPLACE

Before SQL*Plus 10g, when you SPOOL output from
your SQL*Plus scripts to a file, you always overwrote any existing
files from the start. Although the SAVE command had an APPEND
option, to add the current statement to the end of a script, you
couldn’t append SPOOL output. Now, with Oracle 10g, you can append
output with the APPEND option. The default option is to REPLACE the
spool file for backward compatibility.

Another useful option is CREATE, which will
avoid overwriting a file, if it exists, by returning a SQL*Plus
error:

SQL> spool tmp.out
SQL> select * from dual;

D
–
X

SQL> spool off;
SQL> spool tmp.out create
SP2-0771: File “tmp.out” already exists.
Use another name or “SPOOL filename[.ext] REPLACE”

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!