MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Using a Sparse Index

with 2 comments

My vacation from my blog is officially over. The question that I’m answering today is: How can you pass a set of non-sequential ID values to a function and return a result set? You can solve the problem by passing an ADT (Attribute Data Type) or UDT (User Defined Type) variable into a subquery of a cursor. The subquery leverages the TABLE function to translate the ADT or UDT into SQL result set, which is equivalent to a comma-delimited list of values.

You can also solve this problem with Native Dynamic SQL (NDS). However, the person who posed the question didn’t want to use NDS to build out a variable length list of comma-delimited numbers.

You need to create three object types for this example. They are:

  • a list of numbers
  • a record structure, declared as an object type without methods
  • a list of the record structure

These are the SQL commands to create the required data types:

CREATE OR REPLACE
  TYPE list_ids IS TABLE OF NUMBER;
/
CREATE OR REPLACE
  TYPE item_struct IS OBJECT
  ( item_id       NUMBER
  , item_title    VARCHAR2(80)
  , release_date  DATE );
/
CREATE OR REPLACE
  TYPE item_struct_list IS TABLE OF item_struct;
/

Next, you create a nonsynchronous function. It takes a sparsely populated list of values that map to the surrogate key of the column, which is typically the table’s primary key column. It returns a collection of the item_struct object type. This type of function is an object-table function.

The code follows:

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

CREATE OR REPLACE
  FUNCTION nonsynchronous 
  ( pv_list_ids  LIST_IDS ) RETURN item_struct_list IS
    /* Declare a record data structure list. */
    lv_struct_list  ITEM_STRUCT_LIST := item_struct_list();
 
    /* Declare a sparsely indexed list of film items. */
    CURSOR get_items
    ( cv_list_ids  LIST_IDS ) IS
      SELECT   item_id AS item_id
      ,        item_title
      ||       CASE
                 WHEN item_subtitle IS NOT NULL THEN
                   ': '|| item_subtitle
               END AS item_title
      ,        release_date AS release_date
      FROM     item
      WHERE    item_id IN (SELECT *
                           FROM   TABLE(cv_list_ids))
      ORDER BY item_id;
BEGIN
  /* Lood through the sparsely populated list of numbers. */
  FOR i IN get_items(pv_list_ids) LOOP
    lv_struct_list.EXTEND;
    lv_struct_list(lv_struct_list.COUNT) := item_struct( item_id      => i.item_id
                                                       , item_title   => i.item_title
                                                       , release_date => i.release_date );
  END LOOP;
 
  /* Return the record structure list. */
  RETURN lv_struct_list;
END;
/

The foregoing nonsynchronous function uses a nested query that transforms to a result set on lines 18 and 19. In the execution block of the program, it uses a call to the item_struct structure to capture and assign row values to an element of the lv_struct_list variable.

You can now test the nonsynchronous function with the following query:

COL item_id      FORMAT 9999  HEADING "Item|ID #"
COL item_title   FORMAT A40   HEADING "Item Title"
COL release_date FORMAT A11   HEADING "Release|Date"
SELECT   *
FROM     TABLE(nonsynchronous(list_ids(1002, 1013, 1007)));

The query returns the record set as an ordered list in the result set, like:

Item					       Release
 ID # Item Title			       Date
----- ---------------------------------------- -----------
 1002 Star Wars I: Phantom Menace	       04-MAY-99
 1007 RoboCop				       24-JUL-03
 1013 The DaVinci Code			       19-MAY-06

I hope this answers the question about how to get results sets with sparsely populated ID values.

Written by maclochlainn

May 11th, 2016 at 1:37 am

Posted in Apex,Apex 4,Database Design,DBA,Oracle 11g,Oracle 12c,pl/sql,sql

Tagged with ,

«
»