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
SCOTT@orcl> connect sys/admin as sysdba
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
SQL> set serveroutput on
SQL> select foo from dual;
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;
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!