Unless you have some whiz-bang 3^rd-party utility that you are using, you will on a daily basis use either SQL*Plus or the new iSQL*Plus utilities supplied by Oracle. With the release of Oracle 10g, these two interfaces come with a few new features that we have all been waiting for and a few features that just make them nicer to use. I am only going to touch on the new features that I think are most notable. There are a couple of new features, such as default behavior and compatibility, that I will not focus on and leave you to read the manual. I have tested the new SQL*Plus and iSQL*Plus 10g Clients against pre-10g and 10g instances so that we can quickly determine what new features are dependent on the 10g backend database to be running. Let's begin.

SQL*Plus

File and Path Names with spaces

Probably the least noticeable new feature for SQL*Plus is the ability for you to have spaces in the directory path or within the file name for our SQL files. This option is only available in a Windows environment and requires you to enclose the full path and file name within quotes (" or ').

SQL> @"c:/oracle/admin/tune/session/Current Sessions.sql"

While this might be a neat little feature to have, if you ever want to port your scripts to a UNIX environment they just will not work. I would suggest that you skip this feature if you have a need to port across operating systems.

glogin.sql and login.sql is run after each CONNECT

I personally think this is the greatest part of the new features. Being a DBA that will typically switch between a multitude of databases in any given hour, the ability for the re-execution of the glogin.sql and login.sql scripts to be executed after every successful CONNECT is priceless. Because these scripts use to be executed only on the first startup of SQL*Plus I would never use them; this will definitely change now and if you are not using these scripts and the power they now possess, I would encourage you to re-visit them.

DBMS_OUTPUT and SELECT statement

Your ability to debug procedures, triggers, and functions just got a huge boost. SQL*Plus will now display your DBMS_OUTPUT after the result of a SELECT statement from procedures, triggers, and functions. This is great news because now DBMS_OUTPUT displays right after the execution of a SELECT statement and does not require you to take additional action for this display. To see how this works I have created a table function and executed it within an Oracle 9i environment and then within an Oracle 10g environment. Within the Oracle 9i environment, I was required to initiate an additional DBMS_OUTPUT call to actually kick out the displays generated from within the function. As you can see, within the Oracle 10g environment the displays from the DBMS_OUTPUT calls are displayed immediately after the SELECT from the function.

Oracle 9i Environment

SQL> set serveroutput on 
SQL> CREATE TYPE OUT_TY AS OBJECT (
  2              COl1          VARCHAR2(1))
  3  /
Type created.

SQL> CREATE TYPE OUT_TBL_TY AS TABLE OF OUT_TY;
  2  /
Type created.

SQL> CREATE OR REPLACE FUNCTION OUT_FN
  2           RETURN OUT_TBL_TY PIPELINED IS
  3           PRAGMA AUTONOMOUS_TRANSACTION;
  4  TYPE         ref0 IS REF CURSOR;
  5  cur0         ref0;
  6  out_rec      out_ty := out_ty(NULL);
  7  BEGIN
  8  OPEN cur0 FOR 'select dummy from dual';
  9  LOOP
 10    FETCH cur0 INTO out_rec.col1;
 11    EXIT WHEN cur0%NOTFOUND;
 12    dbms_output.put_line('Display from OUT_FN Line 1');
 13    PIPE ROW(out_rec);
 14    dbms_output.put_line('Display from OUT_FN Line 2');
 15  END LOOP;
 16  CLOSE cur0;
 17  RETURN;
 18  END OUT_FN;
 19  /
Function created.

SQL> SELECT a.col1 FROM TABLE(OUT_FN) a
  2  /
C
-
X

SQL> exec dbms_output.put_line('');
Display from OUT_FN Line 1
Display from OUT_FN Line 2

PL/SQL procedure successfully completed.

Oracle 10g Environment

SQL> set serveroutput on
SQL> CREATE TYPE OUT_TY AS OBJECT (
  2              COl1          VARCHAR2(1))
  3  /
Type created.

SQL> CREATE TYPE OUT_TBL_TY AS TABLE OF OUT_TY;
  2  /
Type created.

SQL> CREATE OR REPLACE FUNCTION OUT_FN
  2           RETURN OUT_TBL_TY PIPELINED IS
  3           PRAGMA AUTONOMOUS_TRANSACTION;
  4  TYPE         ref0 IS REF CURSOR;
  5  cur0         ref0;
  6  out_rec      out_ty := out_ty(NULL);
  7  BEGIN
  8  OPEN cur0 FOR 'select dummy from dual';
  9  LOOP
 10    FETCH cur0 INTO out_rec.col1;
 11    EXIT WHEN cur0%NOTFOUND;
 12    dbms_output.put_line('Display from OUT_FN Line 1');
 13    PIPE ROW(out_rec);
 14    dbms_output.put_line('Display from OUT_FN Line 2');
 15  END LOOP;
 16  CLOSE cur0;
 17  RETURN;
 18  END OUT_FN;
 19  /
Function created.

SQL> SELECT a.col1 FROM TABLE(OUT_FN) a
  2  /
C
-
X

Display from OUT_FN Line 1
Display from OUT_FN Line 2

Next

Back to DBAsupport.com