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

By James Koopmann

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


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