New Features in Oracle 10g for SQL*Plus and iSQL*Plus

Don’t wait for your shop to get an Oracle 10g database up
and running. Download the client software and start using these great new
features that will make a few of your everyday tasks a bit more bearable.

Unless you have some whiz-bang 3rd-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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles