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

June 3, 2004

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

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