Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

A result cache function returning a collection

with one comment

The following example could easily be replaced with an ordinary query but it shows you how to implement a result cache function. The function returns a delimited set of values as a collection of strings.

1. You need to create a collections of scalar datatypes are varray or nested table user-defined types (UDT) in the database catalog:

CREATE OR REPLACE TYPE lookup IS TABLE OF VARCHAR2(325);
/

2. You create the result cache function by using a bulk fetch of a set of tokenized strings into a UDT collection variable:

CREATE OR REPLACE FUNCTION get_common_lookup
( table_name VARCHAR2, column_name VARCHAR2 ) RETURN LOOKUP
RESULT_CACHE RELIES_ON(common_lookup) IS
lookups LOOKUP;
CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
SELECT common_lookup_id||'|'||common_lookup_type||'|'||common_lookup_meaning
FROM common_lookup
WHERE common_lookup_table = UPPER(table_name_in)
AND common_lookup_column = UPPER(table_column_name_in);
BEGIN
OPEN c(table_name, column_name);
LOOP
FETCH c BULK COLLECT INTO lookups;
EXIT WHEN c%NOTFOUND;
END LOOP;
RETURN lookups;
END get_common_lookup;
/

3. You can parse the rows of the collection by using regular expressions against the returned rows:

SELECT SUBSTR(
REGEXP_SUBSTR(l.column_value,'^([[:alnum:]])+([|])',1,1),1
, LENGTH(REGEXP_SUBSTR(l.column_value,'^([[:alnum:]])+([|])',1,1)) - 1) AS lookup_id
, SUBSTR(
REGEXP_SUBSTR(l.column_value,'([|])([[:alnum:]]|_)+([|])',1,1),2
, LENGTH(REGEXP_SUBSTR(l.column_value,'([|])([[:alnum:]]|_)+([|])',1,1)) - 2) AS lookup_type
, SUBSTR(
REGEXP_SUBSTR(l.column_value,'([|])(([[:alnum:]])|:| )+$',1,1),2
, LENGTH(REGEXP_SUBSTR(l.column_value,'([|])(([[:alnum:]])|:| )+$',1,1)) - 1) AS lookup_type
FROM (SELECT column_value
FROM table(get_common_lookup('ITEM','ITEM_TYPE'))) l;

The regular expression makes manipulating the COLUMN_VALUE variable possible, provided you know the trick of reading it from an inline view.

Written by maclochlainn

May 13, 2008 at 1:43 am

One Response

Subscribe to comments with RSS.

  1. […] 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. « PHP code to read a PL/SQL […]


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: