Data Management

Learn about SQL*Plus 10g's new features

Get a rundown of the new features in the Oracle SQL*Plus product. For instance, one useful feature is the DBMS_OUTPUT enhancement, which helps you debug functions used in SELECT statements.

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!

Editor's Picks