Oracle Database 10g Release 2 – SQL and PL/SQL Features

Arup Nanda writes about new features for the upcoming Oracle 10gR2 release, targeted at the end of June 2005. In this article, he discussues new SQL and PL/SQL features. This is very interesting stuff; the compilation of things I had been hearing from different sources over the last few months. And of course features that will quickly make it into the Oracle 7Up Workshop (and maybe the Oracle Quiz on next year’s ODTUG conference).

See the full article at: http://www.oracle.com/technology/pub/articles/10gdba/nanda_10gr2dba_part1.html

Error Logging Clause – Sort of an Exceptions Clause for Insert statements

It seems that, at least for INSERTS the article is not clear on UPDATEs, I finally get what I want: using the Error Logging Clause, any record participating in an INSERT statement can violate integrity constraints and the statement can still succeed. The records that are in violations are logged into an Error Logging Table, very much like the Exceptions table that can be used in the Alter Table Add Constraint or the Alter Constraint Enable clause to have existing records violating new constraint being reported in a error table.

I am immediately wondering two things with this clause:
– will it also work with Update and Delete?
– will it also work with Deferred Constraints? (and if so, how will it work out: when I set all constraints immediate at the end of a transaction, will it save the violating records to the error-logging table? And if I just go ahead and commit: will it do the same?

And: can I add a trigger to the error-logging table (I suppose so)?

Conditional PL/SQL Compilation – compiler pre-process instructions

This opens up a world of possibilities. You can add “Annotations� to the PL/SQL, instructing the PL/SQL compiler with conditional logic to include or exclude certain pieces of code, depending on environment settings. This means that when a Package is recompiled with different environment settings, the result will be different.

This allows for example to include or exclude function and procedures as publicly availably in your package specification, depending on whether you are in Unit Test mode or some other mode (Development, Production etc.). That way you can test program units that are really meant to be private without having to incorporate test-framework related code in your package.

It also allows us to embed instrumentation (debugging, trace, logging) code that is only activated upon certain settings. We do not have to check in the PL/SQL code itself for each debug statement whether perhaps the configuration settings are telling us to actually do the debugging (if log_level < = debug then debug('sdjdshdh')). Instead we can have the compiler make that decision for us:

$if $$debugmode $then
   debug('sdjdshdh');
$end

We use

alter session set plsql_ccflags = 'debugmode:TRUE';

followed by a recompile of the package to have the debugging enabled.

In his second example, Arup shows how you even switch on or off pieces of PL/SQL code by simply changing a value in a package. So instead of having to recompile the package to activate or deactivate certain conditional pieces of logic, we can simply change a package value:

$if central_package.debugmode $then
   debug('sdjdshdh');
$end

Activate debugging:

Begin
 Central_package.debugmode:= true;
End;

Calling the central_package is enough to switch debugmode on or off; no recompile is required. However, I am not sure what exactly is different with this than the following statement, without the preprocessing instructions for the compiler:

if central_package.debugmode then
   debug('sdjdshdh');
end;

DBMS_OUTPUT.PUT_LINE improvements

Two very simple yet long desired improvements for dbms_output.put_line:
If you simply specify SET SERVEROUTPUT ON, you are no longer restricted to any buffer size (not the default of 2000, nor any other limit imposed by Oracle). So no more ORU-10027: buffer overflow
Errors.

Second, the limit of 255 characters on the dbms_output.put_line has been removed. Now you can feed this procedure any number, only limited by the 32768 limit of VARCHAR2 variables in PL/SQL.

The standard way to search through XML data: XQuery

Oracle used to provide SQL/XML to query XML data from within SQL Statements. This was a standard from the ANSI committee if I remember correctly. As it turns out, the W3C standard XQuery has surpassed SQL/XML and is now seen as the most important syntax for expressing searches on XML. Oracle 10gR2 supports XQuery in full, allowing us to use so called FLOWR (for, let, order, where, return) expressions in Query statements.

XQuery will always search through XML Documents; the result will also always be XML, but in Oracle 10gR2 we can the xmltable operator to have Oracle interpret the XML query result fragment as an intermediate relational resultset. This is done in very much the same way as the table operator can have the SQL engine use a Collection (nested table) returned from a PL/SQL function as an intermediate result set.

For example:

Alter table emp add (resume_details xmltype)
/
Update emp
Set   resume_details = xmltype(
   '
      Nieuwegein
      Kindergarten
   '
Where ename='KING'
/

select ename
,      XMLQuery
       ( 'for $i in /Resume
          where $i/Residence != "Rotterdam"
          order by $i/Residence
          return $i/HighestFormalEducation
         '
          passing by value RESUME_DETAILS
          returning content
       ) XMLResult
From   emp

Returns:

Ename    XMLResult
------   -----------------------
King       Kindergarten

Using the xmltable operator:

select emp.ename
,      Resume.column_value “Highest Education�
from   emp
,      XMLTable
       ( 'for $root in $emp
          where $root/Resume/Residence != "Rotterdam"
          order by $root/Resume/Residence
          return $root/Resume/HighestFormalEducation/text()
         '
          passing emp.RESUME_DETAILS as emp
       ) Resume

And the result should be fairly similar to the previous query, this time returning plain text:

Ename    Highest Education
------   -----------------------
King       Kindergarten