Learn about SQL*Plus 10g's 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

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. For example:

SQL> create table tmp (a char);
Table created.
SQL> drop table tmp;
Table dropped.
SQL> show recycle

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"

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.