Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jun 3, 2004

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

By James Koopmann


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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date