Oracle 10g SQL New Features (Part 2)

There are too many Oracle 10g SQL new features to be able to cover them all so we've picked out some of the more interesting ones as well as a few in SQL*Plus .

Part 1 of this series of articles about the Oracle 10g SQL new features covers:

• writeable external tables
• restore points and flashback queries
• regular expression support
• query versions 

SET SERVEROUTPUT to allow and default to an UNLIMITED size and the increase in the  line length limit of DBMS_OUTPUT to 32767 bytes from 255. So no more losing half of your debug output when running pl/sql programs!

---

Another new feature of interest in Oracle 10g SQL*Plus is the ability to have white space in file names referenced by the spool, run, @ and @@ commands
eg. SQL> SPOOL "LIST OF TABLES IN MY SCHEMA"

Case/Accent-Independent Sorts In Oracle 10g SQL

If you have ever wanted to sort output from a query independently of whether the results were in upper case, lower case or mixed (i.e. you didn't care about the case), with this new feature, now you can.

You can achieve this either by changing the NLS_SORT parameter or by using the NLSSORT function as shown in the following examples:

HR> select last_name ,email from hr.emp
  2 where lower(last_name)='taylor' order by last_name;

LAST_NAME EMAIL
--------- ----------
taylor    JTAYLOR
taylor    WTAYLOR
Taylor    WTAYLOR
Taylor    JTAYLOR
TAYLOR    WTAYLOR
TAYLOR    JTAYLOR

HR> alter session set nls_sort=generic_m_ci;
-- case insensitive sort

HR> select last_name ,email from hr.emp
  2 where lower(last_name)='taylor' order by last_name;

LAST_NAME EMAIL
--------- ----------
Taylor    JTAYLOR 
Taylor    WTAYLOR
TAYLOR    WTAYLOR
taylor    WTAYLOR
TAYLOR    JTAYLOR
taylor    JTAYLOR

HR> alter session set nls_sort=generic_m; -- case sensitive sort

HR> select last_name ,email from hr.emp WHERE LOWER(LAST_NAME)='taylor'
  2  order by nlssort(last_name,'nls_sort=generic_m_ci');
    -- make sort case insensitive

LAST_NAME EMAIL
--------- ----------
Taylor    JTAYLOR 
Taylor    WTAYLOR
TAYLOR    WTAYLOR
taylor    WTAYLOR
TAYLOR    JTAYLOR
taylor    JTAYLOR

The "_ci" suffix changes the sorting order to be case insenstive. Notice how the order of the rows in the output changed with the 2nd and 3rd queries. For accent-independent sorts, the suffix used is "_ai"

Aggregates In SQL "Returning" Clause

The returning clause is used to return the value of a column in a row affected by an insert, update or delete statement. With Oracle 10g SQL and above you can now return an aggregate value such as the sum of the specified columns in the affected rows. For example:

DECLARE
    sal NUMBER;

BEGIN
    DELETE FROM emp WHERE last_name = 'Taylor'
    RETURNING SUM(salary) INTO sal;
    DBMS_OUTPUT.PUT_LINE('Salary bill reduced by '||sal);
END;
/
Salary bill reduced by 5900

Send us your questions/feedback What Oracle technologies would like to learn about? What aspect of oracle causes you the most trouble? Please note that all fields followed by an asterisk must be filled in. Comments/Questions:* Comments/Questions:* Please enter the word that you see below.

We appreciate your feedback please email your comments to us at Oracle_Tips_and_Tricks@asktheoracle.net

---

Looking for Oracle training?

With our partners we offer instructor-led Oracle training in the UK and New Zealand. See here for Oracle training in NZ or here for Oracle training in the UK

Return to Oracle 10g new features overview