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
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
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;
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
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.
Alter table emp add (resume_details xmltype) / Update emp Set resume_details = xmltype( '<resume> <residence>Nieuwegein</residence> <highestformaleducation>Kindergarten</highestformaleducation> </resume>' 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
Ename XMLResult ------ ----------------------- King <highestformaleducation>Kindergarten</highestformaleducation>
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
- Oracle 10g's Finest – The Top 3 SQL and PL/SQL Features new in 10g – paper for ODTUG 2005
- Book Review: Oracle Database 10g New Features – Oracle10g Reference for Advanced Tuning and Administration by Daniel Liu, Don Burleson, Madhu Tumma, Mike Ault
- Discussion on The Server Side: Comparing Microsoft Yukon (SQL Server 2005, Beta release) with Oracle 10g
- Oracle (finally) announced release of Enterprise Planning and Budgeting
- Oracle Warehouse Builder 10g Paris Release â€¦ Oh ja, ook voor Data Warehouses! (article on the OWB 10gR2 Paris Release)