Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Result cache functions in Oracle 11g return what?

with one comment

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.


Written by maclochlainn

May 13, 2008 at 2:04 am

One Response

Subscribe to comments with RSS.

  1. First link is working and it is really great article. Thanks for sharing. Second link about runstats doesn’t working. Please check.

    Jack Nicholson

    April 12, 2010 at 6:55 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: