logo for asktheoracle.net
leftimage for asktheoracle.net

Oracle 11g SQL New Features

As with every new release from Oracle, there are too many new features in Oracle 11g SQL to be able to cover them all so as we did with our discussion of the Oracle 10g new features we've picked out the more interesting ones and we've also included the SQL*Plus new features.

This series of articles about the Oracle 11g SQL new features covers:
  • SQL*Plus new features 
  • Data warehousing enhancements
  • Performance improvements
  • Enhancements to Regular Expressions
  • Miscellaneous improvements

 This article outlines:
  • SQL*Plus new features
  • Enhancements to regular expressions in SQL (and PL/SQL).

Oracle 11g SQL*Plus New Features

The changes to SQL*Plus are quite modest which is probably not surpising as it's a relatively simple tool and has been 
around for so long. However there are a couple of very useful new features as we'll see. First off are the new set commands:
  • set esschar
  • set errorlogging

Set esschar

This new command enables you to escape special characters (@,?,% and $) when used in file names. This works when loading/running files (scripts) and when spooling. For example the following commands allow you to save output to a file called files@.lst

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.



SQL>set esschar @
SQL>spool "files@.lst"

I see this as being very valuable - especially for playing tricks on your colleagues who don't know about this new feature!

Set errorlogging

This new command is actually very useful - no more needing to define your own table for error logging or missing errors from SQL or SQL*Plus scripts.

As the name suggests, this command traps all errors generated by SQL*Plus, SQL and PL/SQL in the session and store them along with all the associated details in a table called SPERRORLOG in the current schema. The table is created if it doesn't already exist at the point when error logging is enabled. It is also possible to define your own table to be used for error logging and this can be in a different schema.

When an error occurs the username, time, error message, statement causing the error, an optional user-defined identifier and the name of the script being run, if applicable, are stored in the error log automatically and independently of any commits or save points.

Oracle 11g SQL Regular Expressions

Support for the use of POSIX regular expressions in both SQL and PL/SQL was introduced with Oracle 10g and Oracle 10g release 2 added support  for common Perl-style regular expressions. (Follow this link for an introduction to the use of regular expressions in Oracle 10g).

In Oracle 11g SQL, regualr expressions have been enhanced further to allow for subexpressions when using regexp_substr and regexp_instr and to be able to count the occurences of a regular expression in a string by use of the regexp_count function.

regexp_instr

Let's have a look at a couple of examples to see how these work. The first example searches for the 2nd subexpression (i.e. david) in the given string and returns the position of the 1st character of the 1st occurrence (i.e. 5).

  select regexp_instr('markdavidanthonyjuliancliverobertjackbilledward'
  ,'(mark)(david)(anthony)(julian)(clive)(robert)(jack)(bill)'
  ,1  -- start searching from 1st character
  ,1  -- find first occurence of pattern
  ,0  -- return position of 1st character of match
  ,'i' -- case insensitive search
  ,2 -- search for 2nd subexpression (david)
  ) position from dual

  POSITION
----------
    5

A couple of points to note:
  1. the subexpressions can include meta characters
  2. the subexpressions must be in the same order and can't be longer than the string to search. This means if we removed the 1st sub expression (mark)or added another subexpression to the end of our list we would not find a match.

regexp_count

This next example demonstrates the use of regexp_count and asks how many times the substring 'an' is found in the search string. The answer in this case is 2: once in 'anthony' and again in 'julian'
  1  select regexp_count('markdavidanthonyjuliancliverobertjackbilledward'
  2  ,'an' -- regular expression to be counted
  3  ,1  -- start searching from 1st character
  4  ,'i' -- case insensitive search
  5* ) count from dual

     COUNT
----------
      2

If we were to change this to search for 'aa' (by replacing 'an' with 'aa' on line 2), the answer would be 0 - meaning that 'aa' is not in our search string.

The exact syntax of these functions can be found in the Oracle 11g SQL Reference. These functions can also be used in exactly the same way in PL/SQL.

Looking for Oracle 11g Training?

With our partners Smartsoft we offer instructor-led Oracle training on or off site in the UK as well as the full Oracle dba certification track via the Internet.  See here for Oracle training in the UK. Or contact us for details of our on-line training courses.