MacLochlainns Weblog

If you implement street address as a nested table (or collection), the problem is printing an address book using only a SQL statement. The problem comes from matching up the multiple line return from the TABLE function with the rest of the row. That’s not a problem when you denormalized the list into known columns.

A PL/SQL function can convert the list into a scalar value. It’s an easy way to leverage a nested table. You can find the full code here. You might think that this is a neat opportunity to use a result cache function. I did, but the behavior isn’t presently supported. On compilation of a function with the RESULT_CACHE clause and a scalar collection input parameter, you raise a PLS-00999 error.

The message tells you:

RESULT_CACHE IS disallowed ON subprograms WITH IN parameter OF (OR containing) nested TABLE TYPE

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.