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

Thursday Jun 3rd 2004 by James Koopmann
Share:

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!

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


SET SQLPROMPT



A few more runtime variables have been introduced to help you identify the instance you are connected to and as which user you have connected. All I can say to Oracle is thanks for this one. As we all switch from one database instance to the next it is very easy to get lost--especially when you are connected to production and test instance and have multiple windows up on your workspace. I know it is a bit difficult, but if Oracle could just change the background color on my windows, I would be totally happy. For now, this is as good as it gets. Keep in mind that these variables are available for use in any manner that you would normally use variables. The following new variables are as follows:



_DATE : the current date or a pre-defined string.
_PRIVILEGE : level of connection, SYSDBA, SYSOPER, or NULL for normal.
_USER : user you are connected to.



SET SQLPROMPT Example

SQL > SET SQLPROMPT "_DATE : _USER'@'_CONNECT_IDENTIFIER _PRIVILEGE > "
20-MAY-04 : SYSTEM@prd9i  >

Also, be aware the syntax has changed a bit on these. Here is an example of how it used to be in pre-10g and current 10g for the CONNECT_IDENTIFIER.

SQLPROMPT Switching Between Instances (pre-Oracle10g)

SQL > SET SQLPROMPT '&_CONNECT_IDENTIFIER > '
prd9i > 

prd9i > connect system@prd10g
Enter password:
Connected.
prd9i > 

SQLPROMPT Switching Between Instances (new-Oracle10g)

SQL >  SET SQLPROMPT "_CONNECT_IDENTIFIER > "
prd9i > 

prd9i > connect system@prd10g
Enter password:
Connected.
prd10g >

As a side note, if you are using the glogin.sql script and have the pre-10g CONNECT_IDENTIFIER since the glogin.sql script is now executed after every successful connection as previously discussed, you do not need to worry about the proper prompt being displayed.

SQLPROMPT Switching Between Instances with pre-Oracle10g & _CONNECT_IDENTIFIER in globin.sql

SQL > connect system@prd10g
Enter password:
Connected.			
prd10g > connect system@prd9i
Enter password:
Connected.
prd9i >

SPOOL Command Enhancement

The SPOOL command has now been altered to handle the ability to append to the end of a previously spooled file. There is really only one new option for this command since the CREATE and REPLACE behave just as with no option and the behavior we are all use to. The new options to the SPOOL command are as follows:

CREATE : Create a new spool file
REPLACE : Create a new spool file
APPEND : Append to the given spool file

SPOOL Example

prd9i > spool dual.lst
prd9i > select * from dual;
D
-
X
prd9i > spool off

Display the spooled file.

prd9i > ho cat dual.lst
prd9i > select * from dual;
D
-
X
prd9i > spool off

SPOOL APPEND to the previously created spooled file

prd9i > spool dual.lst append
prd9i > select * from dual;
D
-
X
prd9i > spool off

Display the appended spooled file

prd9i > ho cat dual.lst
prd9i > select * from dual;
D
-
X
prd9i > spool off
prd9i > select * from dual;
D
-
X
prd9i > spool off


Prompting from within iSQL*Plus


Oracle has now added the ability for you to cause iSQL*Plus to prompt the user for input. This is a great step in getting us away from the ole SQL*Plus command line and into the "sexier" GUI interface of iSQL*Plus. In addition, this now allows you to port those scripts that you would typically run from within SQL*Plus that had prompts, into this new interface without any problems. For those end users that are afraid of a command line interface, this provides some confidence and familiarity they can live with. I have, in the past, been reluctant to use iSQL*Plus just for this reason of no prompt ability--now I just might switch. As a side note, I really like the fact that Oracle displays the value I put in the prompted value.


SQL Within iSQL*Plus with a prompted value

Click for larger image

iSQL*Plus asking for the value of our prompted value


iSQL*Plus after execution showing the prompted value we entered


Change in DESCRIBE Behavior (A 10g only thing)

It used to be that when you tried to DESCRIBE an object that was INVALID, Oracle would just tell you the object was invalid. Now, in Oracle 10g when you DESCRIBE an INVALID object, Oracle will attempt to re-compile and validate it. If Oracle is able to validate the object the DESCRIBE command will continue as expected and show the structure of the object.

The features I have described thus far are available if you have a 10g client, it does not require a 10g database instance. The next feature, DESCRIBE Behavior, is only available if you have both a 10g client and a database instance available. Below I have given you examples of an Oracle pre-10g instance and how the DESCRIBE command behaves through the Oracle 10g Client as well as an Oracle 10g Client and Instance.

DESCRIBE Behavior for Oracle pre-10g

SQL> create table t (col1 number);
Table created.

SQL> create or replace PROCEDURE PROCDisplay (customer varchar2) IS
  2  var number;
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('Display1');
  5  select col1 into var from t;
  6  END PROCDisplay;
  7  /
Procedure created.

SQL> desc procdisplay
PROCEDURE procdisplay
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CUSTOMER                       VARCHAR2                IN

SQL> drop table t;
Table dropped.

SQL> desc procdisplay
ERROR:
ORA-24372: invalid object for describe

SQL> create table t (col1 number);
Table created.

SQL> desc procdisplay
ERROR:
ORA-24372: invalid object for describe

DESCRIBE Behavior for Oracle 10g

SQL> create table t (col1 number);
Table created.

SQL> create or replace PROCEDURE PROCDisplay (customer varchar2) IS
  2  var number;
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('Display1');
  5  select col1 into var from t;
  6  END PROCDisplay;
  7  /
Procedure created.

SQL> desc procdisplay
PROCEDURE procdisplay
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CUSTOMER                       VARCHAR2                IN

SQL> drop table t;
Table dropped.

SQL>  desc procdisplay
ERROR:
ORA-24372: invalid object for describe

SQL> create table t (col1 number);
Table created.

SQL>  desc procdisplay
PROCEDURE procdisplay
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CUSTOMER                       VARCHAR2                IN

Take Advantage with the 10g Client

These small changes to the SQL*Plus and iSQL*Plus environments really round these products out nicely. We surely could live without these new features but life sure will be easier from this point on. The features may seem small and non-important but after you begin using them, you will not know how you lived without them.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved