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
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
commands allow you to save output to a file called files@.lst
& 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
I see this as being very valuable - especially
for playing tricks on your colleagues who don't know about this new
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
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
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
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
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 to be able to count the occurences of a regular expression in a
string by use of the regexp_count
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).
-- start searching from 1st character
-- find first occurence of pattern
-- return position of 1st character of match
-- case insensitive search
-- search for 2nd subexpression (david)
position from dual
A couple of points to note:
- the subexpressions can include meta characters
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
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'
-- regular expression to be counted
-- start searching from 1st
-- case insensitive search
5* ) count from dual
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
in our search string.
The exact syntax of these functions can be found in the Oracle 11g SQL Reference
functions can also be used in exactly the same way in PL/SQL.
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
for details of our on-line, on demand, training courses.