Archive for the ‘result_cache’ tag
Result cache functions disallow nested table input parameters
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 |
Result cache functions in Oracle 11g return what?
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.