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: • set esschar • set errorlogging

Set esschar

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 called “files@.lst” SQL>set esschar @ SQL>spool "files@.lst"

Set errorlogging

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.

regexp_instr

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 example). SELECT regexp_instr('colinclivechirscharlescameronadamandrewarth ur' -- string to be searched                              ,'(colin)(clive)(chirs)(charles)(cameron)(adam)(andrew)(a rthur)' -- list expressions           ,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)          ) position FROM DUAL POSITION  ----------      11 Note that a subexpressions (the string to search for) can include meta characters.

regexp_count

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                         ) num_awks_found FROM DUAL  num_awks_found  ---------------        0 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 to individuals. View woopra privacy policy.     View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.