MacLochlainns Weblog

Somebody asked for a URI reference to the PL/SQL code for my Oracle Database 11g PL/SQL ProgrammingOracle Database 11g PL/SQL Programming book. You can find it here on the McGraw-Hill site. If you find any glitches, just update my blog. I’ll fix them and get them reposted.

Also, I’ve updated the Comparative Data Modeling Relational versus XML with screen shots for the code and the mapping of a FLOWR (For, Let, Order by, Where, and Return) statements to nested loops for PL/SQL.

There were two broken links in earlier blogs because UTOUG moved their presentation download repository. I’ve updated those links for my Utah Oracle User Group Training Days 2008 presentations. You can also uses these new links: PL/SQL 11g New Features and an Oracle LOBs. You can learn more about UTOUG and prior presentations at http://www.utoug.org, including Tom Kyte’s Materialized Views presentation from the same 2008 spring conference.

Adrian Billington wrote an excellent article on how to use the Oracle 11g PL/SQL new cross session result cache. He uses Tom Kyte’s runstats tuning kit to show the best performance advantage of this new feature. The recommendation is converting functions that perform SQL lookups to result cache functions. A result cache function’s actual parameters and results are cached only once in the SGA and available across sessions.

A result cache function can only support SQL datatypes. This limits you to returning only a scalar or collection of a scalar datatype from your function. Collections of scalar datatypes are varray or nested table user-defined types (UDT). When you try to return an aggregate table from a result cache function, Oracle 11g Release 1 raises a PLS-00999 error. The error signals an implementation restriction that may be temporary. Let’s hope it changes in Oracle 11g Release 2.

You can cheat the limitation by returning a collection of tokenized strings but I’m not sure it’s worth the effort. If you think it’s worth the effort, you can check out the code.

Last month I presented the Oracle PL/SQL 11g New Features at the Utah Oracle User’s Group (UTOUG) Training Days. You can find the slides here. My favorite new feature is that you no longer have to select a sequence into a variable to use it in a SQL statement in a PL/SQL block.