Oracle Consulting and Training
For Better, Faster, Smarter
Oracle 11g SQL New Features
The Oracle 11g SQL new features we’ve selected to feature are the enhancements to regular expressions in
SQL (and PL/SQL), SQL*Plus new features and the new features in SQL for data warehouses.
Oracle 11g SQL*Plus
SQL*Plus has some new SET commands which are very useful:
This new command enables you to use special characters
(@,?,% and $) in file names both for scripts and spool files.
For example the following commands direct output to a file
SQL>set esschar @
Now you need never miss any SQL*Plus or SQL errors again. With error logging enabled, Oracle
automatically traps any SQL*Plus, SQL or PL/SQL errors in the session and stores them in a table along with
the username, time, error message, statement causing the error, an optional user-defined identifier and the
name of the script being run, if applicable.
SQL>set errorlog on
By default, errors are stored in a table called SPERRORLOG in the current schema. If SPERRORLOG doesn't
exist at the point when error logging is enabled it is created automatically by Oracle.
You can also define your own table for error logging and this can be in a different schema by suffixing the
command with the schema and table of your table.
SQL>set errorlog on myschema.mytable
When an error occurs the details are stored in the error log automatically and independently of any commits
or save points.
See the Oracle 11g SQP*Plus User Guide and Reference for the full syntax of the new SET commands.
Oracle 11g SQL Regular Expressions
Support for the use of POSIX regular expressions in SQL and PL/SQL was introduced with Oracle 10g and
11g SQL now supports subexpressions with 2 new functions regexp_substr and regexp_instr and
for counting the occurences of a regular expression in a string with another new function regexp_count.
Let's look at an example to show how this works. This example searches for the 4th subexpression (’charles’,
in this case) in the given string and returns the position of the 1st character of the 1st occurrence (11 in this
SELECT regexp_instr('colinclivechirscharlescameronadamandrewarthur' -- string to be
,'(colin)(clive)(chirs)(charles)(cameron)(adam)(andrew)(arthur)' -- list
,1 -- search from 1st character
,1 -- find first occurrence of pattern
,0 -- return position of 1st character of match
,'i' -- ignore case
,4 -- search for 4th subexpression (charles)
Note that a subexpressions (the string to search for) can include meta characters.
This example shows how to use the function regexp_count to return the count of the occurrences of the
substring 'awk' is found in the search string, which in this case is 0 (as that doesn’t occur in the search string).
SELECT regexp_count('clivechirscharlescameronadamart' -- string to be searched
,'awk' -- regular expression to be counted
,1 -- start searching from 1st character
,'i' -- ignore case
If we were to substitute ‘am’ for ‘awk’ in the regular expression, the answer would be 2 - meaning that 'am'
occurs twice in our search string.
See the Oracle 11g SQL Reference for the full syntax of these functions which can be used in PL/SQL in
exactly the same way.
Looking to sky-rocket productivity, save time and reduce costs?Training is a highly cost-effective and
proven method of boosting productivity. Smartsoft offers instructor-led training in Oracle and related
technologies on or off site in cities across the UK as well as self-study online training. See our scheduled
Oracle training courses, or let us know your requirements.
Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket
productivity and make your systems better, faster and smarter.
© Smartsoft Computing Ltd, Bristol, England
Need help? Contact Us
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective