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
–
XSQL> exec dbms_output.put_line(”);
Display from OUT_FN Line 1
Display from OUT_FN Line 2PL/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
–
XDisplay from OUT_FN Line 1
Display from OUT_FN Line 2