SQL and PLSQL Regular Expressions

Page 164

   FOR rec IN 1..v_fam.LAST()    LOOP       IF v_fam(rec).emp_dep_code!=0       THEN          v_fam.delete(rec);       END IF;    END LOOP;    FORALL inds IN INDICES OF v_fam       INSERT INTO employees VALUES v_fam(inds); END; / 

Exception Handling

In Oracle Database 10g, the PL/SQL engine allows you to handle exceptions raised during the execution of a forall statement. This allows your code to be written in such a way that an error does not need to halt its execution. You can also save information about the error after it occurs, write it to a table, or display it. The Oracle documentation provides some good examples of this feature.

SQL and PL/SQL Regular Expressions

Regular expressions are new features in Oracle Database 10g. This section looks at what regular expressions are, and how regular expressions can be used in Oracle Database 10g, including a review of new functions that are used in association with regular expressions.

What Are Regular Expressions?

If you have done much scripting in UNIX, then no doubt you are well acquainted with regular expressions. A regular expression is a set of symbols and elements of syntax that allows you to match patterns of text within a given expression. Regular expressions are very powerful and allow for pattern matching that was previously only possible through a programmatic solution.

Oracle Database 10g now supports the use of regular expressions in SQL and PL/SQL. The definitions for these regular expressions are defined within the POSIX standard regular expression syntax. Regular expressions use a combination of metasymbols to define various matching and search criteria. Note that matching criteria are case sensitive when using regular expressions. The most commonly used metasymbols are listed and described in Table 7-1.

Using Regular Expressions in Oracle Database 10g

Regular expressions in Oracle Database 10g are supported through the use of a number of new functions, which are described in more detail in the following sections.