New Features in Oracle 10g for SQL*Plus and iSQL*Plus
June 3, 2004
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.
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 ').
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
Oracle 10g Environment