MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Object Record Collections

without comments

It must have been disgust with learning that a Result Cache function couldn’t use an object type that made me zone on showing how to use an object type as the return type of a PL/SQL table function. The nice thing about this approach, as pointed out by Gary Myer’s comment on another blog post, is that it doesn’t require a pipelined function to translate it from PL/SQL to SQL scope.

The first step is to create an object type without a return type of SELF, which Oracle elected as its equivalent to this for some unknown reason. A user-defined type (UDT) defined without a return type, returns the record structure of the object, but as mentioned it is disallowed in result cache functions. After you define the base type, you create a collection of the base UDT. Then, you can use the UDT as a SQL return type in your code, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE OR REPLACE FUNCTION get_common_lookup_object_table
( table_name  VARCHAR2
, column_name VARCHAR2 )
RETURN common_lookup_object_table IS
 
  -- Define a dynamic cursor that takes two formal parameters.
  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);
 
  -- Declare a counter variable.
  counter INTEGER := 1;
 
  -- Declare a package collection data type as a SQL scope table return type.
  list COMMON_LOOKUP_OBJECT_TABLE := common_lookup_object_table();
 
BEGIN
 
  -- Assign the cursor return values to a record collection.
  FOR i IN c(table_name, column_name) LOOP
    list.extend;
    list(counter) := common_lookup_object(i.common_lookup_id
                                         ,i.common_lookup_type
                                         ,i.common_lookup_meaning);
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_object_table;
/

You can then query it in SQL like this:

COLUMN common_lookup_id      FORMAT 9999 HEADING "ID"
COLUMN common_lookup_type    FORMAT A16  HEADING "Lookup Type"
COLUMN common_lookup_meaning FORMAT A30  HEADING "Lookup Meaning"
 
SELECT   *
FROM     TABLE(get_common_lookup_object_table('ITEM','ITEM_TYPE'));

This depends on the same sample code that I use elsewhere on the blog. You can download it from McGraw-Hill’s web site. You can also find a complete and re-runnable script by clicking on the down arrow below.

Code Script ↓

BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name = 'GET_COMMON_LOOKUP_OBJECT_TABLE') LOOP
    EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
  END LOOP;
  FOR i IN (SELECT   type_name
            FROM     user_types
            WHERE    type_name = 'COMMON_LOOKUP_OBJECT_TABLE') LOOP
    EXECUTE IMMEDIATE 'DROP TYPE '||i.type_name;
  END LOOP;
  FOR i IN (SELECT   type_name
            FROM     user_types
            WHERE    type_name = 'COMMON_LOOKUP_OBJECT') LOOP
    EXECUTE IMMEDIATE 'DROP TYPE '||i.type_name;
  END LOOP;
END;
/
 
CREATE OR REPLACE TYPE common_lookup_object IS OBJECT
( common_lookup_id      NUMBER
, common_lookup_type    VARCHAR2(30)
, common_lookup_meaning VARCHAR2(255));
/
 
CREATE OR REPLACE TYPE common_lookup_object_table IS TABLE OF common_lookup_object;
/
 
CREATE OR REPLACE FUNCTION get_common_lookup_object_table
( table_name  VARCHAR2
, column_name VARCHAR2 )
RETURN common_lookup_object_table IS
 
  -- Define a dynamic cursor that takes two formal parameters.
  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);
 
  -- Declare a counter variable.
  counter INTEGER := 1;
 
  -- Declare a package collection data type as a SQL scope table return type.
  list COMMON_LOOKUP_OBJECT_TABLE := common_lookup_object_table();
 
BEGIN
 
  -- Assign the cursor return values to a record collection.
  FOR i IN c(table_name, column_name) LOOP
    list.extend;
    list(counter) := common_lookup_object(i.common_lookup_id
                                         ,i.common_lookup_type
                                         ,i.common_lookup_meaning);
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_object_table;
/
 
TTITLE OFF
 
COLUMN common_lookup_id      FORMAT 9999 HEADING "ID"
COLUMN common_lookup_type    FORMAT A16  HEADING "Lookup Type"
COLUMN common_lookup_meaning FORMAT A30  HEADING "Lookup Meaning"
 
SELECT   *
FROM     TABLE(get_common_lookup_object_table('ITEM','ITEM_TYPE'));

Written by maclochlainn

March 23rd, 2009 at 12:29 am

Posted in Oracle,pl/sql,sql

«
»