MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Returning a scalar collection

with 3 comments

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
  -- A local variable of the user-defined scalar collection type.
  lookups LOOKUP;
 
  -- A cursor to concatenate the columns into one string with a delimiter.
  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 id
,      SUBSTR(
         REGEXP_SUBSTR(l.column_value,'([|])([[:alnum:]]|_)+([|])',1,1),2
         , LENGTH(REGEXP_SUBSTR(l.column_value,'([|])([[:alnum:]]|_)+([|])',1,1)) - 2) AS TYPE
,      SUBSTR(
         REGEXP_SUBSTR(l.column_value,'([|])(([[:alnum:]])|:| )+$',1,1),2
         , LENGTH(REGEXP_SUBSTR(l.column_value,'([|])(([[:alnum:]])|:| )+$',1,1)) - 1) AS meaning
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 13th, 2008 at 1:43 am

Posted in Uncategorized

3 Responses to 'Returning a scalar collection'

Subscribe to comments with RSS or TrackBack to 'Returning a scalar collection'.

  1. Excellent blog I have ever seen. Special about your blog is that it has examples, not just a theoretical concepts.

    swapnil

    23 Dec 08 at 11:04 pm

  2. I don’t normally comment on blogs, however I have to say that I rather enjoyed yours as it was enlightening.

    Scott H.

    8 Jan 10 at 12:45 am

  3. […] It would be possible to work around this limitation by bundling up the output into a single large delimiter separated string, but, for me, performance issues would have to be significant before resorting to that. Good sample code for doing it that way is here. […]

    PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters « Andrew Fraser DBA

    5 May 10 at 8:44 am

Leave a Reply