MacLochlainns Weblog

The first part of this series showed how you can leverage Oracle’s SQL syntax with UDT columns and collection columns. It would be nice if Oracle gave you some SQL to work with the elements of ADT collections, but they don’t. After all, that’s why you have this article.

While you could change the setup of the prior example table, it’s easier to create a new customer table. The new customer table drops the address column. There’s also a new pizza table. The pizza table includes an ingredient ADT collection column, which by design holds a unique set of ingredients for each pizza.

Realistically, ADT collections of numbers, characters, and dates have little value by themselves. That’s because those data types typically don’t have much meaning. A set of unique strings can be useful for certain use cases.

You create the list ADT type with this syntax:

SQL> CREATE OR REPLACE
  2    TYPE list IS TABLE OF VARCHAR2(20);
  3  /

You create the customer and pizza tables, and customer_s and pizza_s sequences with the following syntax:

SQL> CREATE TABLE customer
  2  ( customer_id  NUMBER
  3  , first_name   VARCHAR2(20)
  4  , last_name    VARCHAR2(20)
  5  , CONSTRAINT pk_customer PRIMARY KEY (customer_id));
 
SQL> CREATE SEQUENCE customer_s;
 
SQL> CREATE TABLE pizza
  2  ( pizza_id     NUMBER
  3  , customer_id  NUMBER
  4  , pizza_size   VARCHAR2(10)
  5  , ingredients  LIST
  6  , CONSTRAINT pk_pizza PRIMARY KEY (pizza_id)
  7  , CONSTRAINT ck_pizza_size
  8    CHECK (pizza_size IN ('Mini','Small','Medium','Large','Very Large')))
  9  NESTED TABLE ingredients STORE AS ingredient_table;
 
SQL> CREATE SEQUENCE pizza_s;

The customer table only has scalar columns. The pizza table has the ingredient ADT collection column. Line 9 creates a nested ingredient_table for the ingredient ADT collection column.

There is a primary and foreign key relationship between the customer and pizza tables. That relationship between the tables requires that you insert rows into the customer table before you insert rows into the pizza table.

The sample script populates the customer table with characters from the Green Arrow television show, as follows:

  Customer
    ID # Last Name  First Name
-------- ---------- ----------
       1 Queen      Oliver
       2 Queen      Thea
       3 Queen      Moira
       4 Lance      Dinah
       5 Lance      Quentin
       6 Diggle     John
       7 Wilson     Slade

Next, you can insert three rows into the pizza table. Each has different ingredients in the ingredient ADT column.

The following is the syntax for the INSERT statements:

SQL> INSERT INTO pizza
  2  VALUES
  3  ( pizza_s.NEXTVAL
  4  ,(SELECT c.customer_id FROM customer c
  5    WHERE  c.first_name = 'Quentin' AND c.last_name = 'Lance')
  6  ,'Large'
  7  , list('Cheese','Marinara Sauce','Sausage','Salami'));
 
SQL> INSERT INTO pizza
  2  VALUES
  3  ( pizza_s.NEXTVAL
  4  ,(SELECT c.customer_id FROM customer c
  5    WHERE  c.first_name = 'Thea' AND c.last_name = 'Queen')
  6  ,'Medium'
  7  , list('Cheese','Marinara Sauce','Canadian Bacon','Pineapple'));
 
SQL> INSERT INTO pizza
  2  VALUES
  3  ( pizza_s.NEXTVAL
  4  ,(SELECT c.customer_id FROM customer c
  5    WHERE  c.first_name = 'John' AND c.last_name = 'Diggle')
  6  ,'Small'
  7  , list('Cheese','BBQ Sauce','Chicken'));

Querying results from tables with nested ADT columns provides interesting results. An ordinary query, like this:

SQL> COL pizza_id     FORMAT 99999  HEADING "Pizza|ID #"
SQL> COL pizza_size   FORMAT A6     HEADING "Pizza|Size"
SQL> COL ingredients  FORMAT A64    HEADING "Ingredients"
SQL> SELECT pizza_id
  2  ,      pizza_size
  3  ,      ingredients
  4  FROM   pizza;

… returns the following results with a flattened object type:

Pizza Pizza
  ID # Size   Ingredients
------ ------ ----------------------------------------------------------------“
     1 Large  LIST('Cheese', 'Marinara Sauce', 'Sausage', 'Salami')
     2 Medium LIST('Cheese', 'Marinara Sauce', 'Canadian Bacon', 'Pineapple')
     3 Small  LIST('Cheese', 'BBQ Sauce', 'Chicken')

If you use a CROSS JOIN it multiplies each row times the number of items in the ADT collection column. The multiplication hides the results.

The best solution for displaying results from an ADT collection requires that you serialize the results. The following serialize_set PL/SQL function creates a serialized comma separated list:

SQL> CREATE OR REPLACE
  2    FUNCTION serialize_set (pv_list LIST) RETURN VARCHAR2 IS
  3      /* Declare a return string as large as you need. */
  4      lv_comma_string  VARCHAR2(60);
  5    BEGIN
  6      /* Read list of values and serialize them in a string. */
  7      FOR i IN 1..pv_list.COUNT LOOP
  8        IF NOT i = pv_list.COUNT THEN
  9          lv_comma_string := lv_comma_string || pv_list(i) || ', ';
 10        ELSE
 11          lv_comma_string := lv_comma_string || pv_list(i);
 12        END IF;
 13      END LOOP;
 14      RETURN lv_comma_string;
 15    END serialize_set;

You can now write a query that uses your PL/SQL function to format the ADT collection column values into a single row. The syntax for the query is:

SQL> SELECT pizza_id
  2  ,      pizza_size
  3  ,      serialize_set(ingredients) AS ingredients
  4  FROM   pizza;

It returns:

Pizza Pizza
  ID # Size   Ingredients
------ ------ -----------------------------------------------------------
     1 Large  Cheese, Marinara Sauce, Sausage, Salami
     2 Medium Cheese, Marinara Sauce, Canadian Bacon, Pineapple
     3 Small  Cheese, BBQ Sauce, Chicken

At this point, you know how to create a table with an ADT collection column and how to insert values. The Oracle documentation says you can only replace the whole content of the ADT column in an UPDATE statement. That’s true in practice but not in principle.

The principal differs because you can write PL/SQL functions that add, change, or remove elements from the ADT collection that works in an UPDATE statement. The trick is quite simple. You achieve it by:

• Passing the current ADT collection as a IN-only mode parameter
• Passing any new parameters when you add or change elements
• Passing any old parameters when you change or remove elements

Now, you will learn how to create the add_elements, change_elements, and remove_elements PL/SQL functions. They let you use an UPDATE statement to add, change, or remove elements from an ADT collection column.

Adding ADT elements with an UPDATE statement

This section shows you how to add elements to an ADT collection column with an UPDATE statement. The add_elements PL/SQL function can add one or many elements to an ADT collection column. That’s possible because the new element or elements are passed to the function inside an ADT collection parameter.

The merit of this type of solution is that you only need one function to accomplish two tasks. The test cases show you how to pass one new element or a set of new elements.

An alternative solution would have you write two functions. One would accept a collection parameter and a variable length string, and the other would accept two collection parameters. Many developers might choose to do that because they would like to leverage overloading inside PL/SQL packages. You should ask yourself one question when you make the decision about your approach to this problem: Which is easier to maintain and use?

The following creates the add_elements PL/SQL function:

SQL> CREATE OR REPLACE
  2    FUNCTION add_elements
  3    ( pv_list     LIST
  4    , pv_element  LIST ) RETURN LIST IS
  5      /* Declare local return collection variable. */
  6      lv_list  LIST;
  7    BEGIN
  8      /* Check for instantiated collection and initialize when necessary. */
  9      IF pv_list IS NULL THEN
 10        lv_list := list();
 11      ELSE
 12        /* Assign parameter collection to local collection variable. */
 13        lv_list := pv_list;
 14        FOR i IN 1..pv_element.COUNT LOOP
 15          /* Check to avoid duplicates, allocate memory and assign value. */
 16          IF NOT list(pv_element(i)) SUBMULTISET OF lv_list THEN
 17            lv_list.EXTEND;
 18            lv_list(lv_list.COUNT) := pv_element(i);
 19          END IF;
 20        END LOOP;
 21      END IF;
 22  
 23      /* Return new collection. */
 24      RETURN lv_list;
 25    END add_elements;
 26  /

Line 3 and 4 define the two parameters of the add_elements function as ADT collections. Line 4 also designates the return type of the function, which is the same ADT collection.

Line 6 declares a local ADT collection variable. You need a local lv_list ADT collection variable because you want to accept two collections and merge them into the local ADT collection variable. Then, you return the local ADT collection variable as the function outcome.

Line 9 checks whether the pv_list parameter is null. Line 10 initializes the lv_list variable when it is null to avoid an unitialized error when you try to assign values to it. Line 13 assigns an initialized ADT collection column’s value to the local lv_list variable. Line 14 starts a loop through the ADT collection you want to add to the ingredient column’s list of values.

Line 16 use the SUBMULTISET set operator to ensure that only new add elements when they don’t already exist in the ingredient ADT collection column. Line 17 allocates memory space in the lv_list variable, and line 18 assigns a new element to it.

You could extend memory for the total count of elements but that would make the index assignment on line 18 more complex. Combining them increments the count of items and lets you use the count as the index value. Line 24 returns the local ADT collection and replaces the original ingredient column value.

The test case for the function should ensure that only unique values are assigned to the ingredient ADT collection column value. This can be done by a three-step test case. The test queries the values in the ADT collection column, updates them, and re-queries them.

The following query shows you the contents of the row:

SQL> SELECT pizza_id, pizza_size
  2  ,      serialize_set(ingredients) AS ingredients
  3  FROM   pizza
  4  WHERE  customer_id =
  5          ( SELECT customer_id FROM customer
  6            WHERE  first_name = 'Quentin' AND last_name = 'Lance' );

It returns:

Pizza Pizza
  ID # Size   Ingredients
------ ------ -----------------------------------------------------------
     1 Large  Cheese, Marinara Sauce, Sausage, Salami

You can update the ADT collection column’s values with the following UPDATE statement. It attempts to add Sausage and Italian Sausage to the list of values. The function should add only Italian Sausage because Sausage already exists in the list of values. When you re-query the row you will see that the add_elements added only the element Italian Sausage.

You would use the following UPDATE statement:

SQL> UPDATE pizza
  2  SET    ingredients =
  3           add_elements(ingredients,list('Italian Sausage','Sausage'))
  4  WHERE  customer_id =
  5          (SELECT customer_id FROM customer
  6           WHERE  first_name = 'Quentin' AND last_name = 'Lance');

Line 3 calls the add_elements PL/SQL function with the ingredient ADT collection column’s value as the first parameter. The second parameter is a dynamically created list of the elements. It contains the element or elements you want to add to the ingredient column’s values.

Re-querying the row, you should see that the UPDATE statement added only the Italian Sausage element to the row. You should see the following output:

Pizza Pizza 
  ID # Size   Ingredients
------ ------ -----------------------------------------------------------
     1 Large  Cheese, Marinara Sauce, Sausage, Salami, Italian Sausage'

As you can see, the call to the add_elements function adds only Italian Sausage to the list of values in the ingredient column, while a comma delimited list of single quote delimited strings allows you to add multiple elements. You add one element by making it the only single quote delimited item in the list constructor call.

Updating ADT elements with an UPDATE statement

This section shows you how to change elements in an ADT collection column with an UPDATE statement. The change_elements PL/SQL function can change one to many elements in an ADT collection column. That’s possible because the change element or elements are passed to the function inside ADT collection parameters.

Unlike the add_elements function, the change_elements function requires an ADT collection parameter and a UDT collection element. The UDT collection needs to hold an old and new value.

The alternative approach would require you to try and synchronize two ADT collection value sets. One would hold all the old values and the other would hold all the new values, and they would both need to be synchronized in mirrored positional order.

You define a pair UDT object type such as the following:

SQL> CREATE OR REPLACE
  2    TYPE pair IS OBJECT
  3    ( old  VARCHAR2(20)
  4    , NEW  VARCHAR2(20));
  5  /

Next, you define a change UDT collection type:

SQL> CREATE OR REPLACE
  2    TYPE change IS TABLE OF pair;
  3  /

You define the change_element function as shown below:

SQL> CREATE OR REPLACE
  2    FUNCTION change_elements
  3    ( pv_list     LIST
  4    , pv_element  CHANGE ) RETURN LIST IS
  5     /* Declare local return collection variable. */
  6     lv_list  LIST;
  7    BEGIN
  8     /* Check for instantiated collection and initialize when necessary. */
  9     IF pv_list IS NULL THEN
 10       lv_list := list();
 11     ELSE
 12       /* Assign parameter collection to local collection variable. */
 13       lv_list := pv_list;
 14       FOR i IN 1..pv_element.COUNT LOOP
 15         /* Check to avoid duplicates, allocate memory and assign value. */
 16         IF NOT list(pv_element(i).old) SUBMULTISET OF lv_list THEN
 17           lv_list.EXTEND;
 18           lv_list(lv_list.COUNT) := pv_element(i).NEW;
 19         END IF;
 20       END LOOP;
 21     END IF;
 22  
 23     /* Return new collection. */
 24     RETURN lv_list;
 25    END change_elements;
 26  /

Line 3 and 4 define the two parameters of the change_elements function. The first pv_list parameter uses the list ADT collection type and the list type that matches the ingredient column’s data type. Line 4 defines a parameter that uses the change UDT collection type, which is a collection of the pair UDT type.

Line 6 declares a local ADT collection variable, such as the add_elements function. The lv_list variable also serves the same purpose as it does in the add_elements function.

Line 9 checks whether the pv_list parameter is null. Line 10 initializes the lv_list variable when it is null to avoid an unitialized error when you try to assign values to it. Line 13 assigns an initialized ADT collection column’s value to the local lv_list variable. Line 14 starts a loop through the ADT collection you want to add to the ingredient column’s list of values.

Line 16 uses the SUBMULTISET set operator to ensure that the old element exists in the ingredient ADT collection column. Line 17 allocates memory space in the lv_list variable, and line 18 assigns the new element to it.

The change_elements function couples the memory allocation with the assignment of new values. Line 24 returns the local ADT collection and replaces the original ingredient column value.

The test case shows you how to pass one old and one new element or a set of old and new elements. The initial query shows you the data before the update:

SQL> SELECT pizza_id, pizza_size
  2  ,          serialize_set(ingredients) AS ingredients
  3  FROM   pizza
  4  WHERE  customer_id =
  5           (SELECT customer_id FROM customer
  6            WHERE  first_name = 'Thea' AND last_name = 'Queen');

It returns:

Pizza Pizza
  ID # Size   Ingredients
------ ------ -----------------------------------------------------------
     2 Medium Cheese, Marinara Sauce, Canadian Bacon

You now update the row with the following query:

SQL> UPDATE pizza
  2  SET    ingredients =
  3           change_elements(ingredients
  4                          ,change(pair(old => 'Italian Sausage'
  5                                      ,NEW => 'Linguica')))
  6  WHERE  customer_id =
  7          ( SELECT customer_id FROM customer
  8            WHERE  first_name = 'Thea' AND last_name = 'Queen' );

When you re-query the row, it shows you the following:

Pizza Pizza
  ID # Size   Ingredients
------ ------ -----------------------------------------------------------
     2 Medium Cheese, Marinara Sauce, Canadian Bacon, Linguica

As you can see, the call to the change_elements function changes onlyItalian Sausage to Linguica in the list of values in the ingredient column, while a comma delimited list of pair UDT values allows you to change multiple elements. You change one element by making it the only pair UDT in the change constructor call.

Removing ADT elements with an UPDATE statement

This section shows you how to remove elements from an ADT collection column with an UPDATE statement. The remove_elements PL/SQL function can remove one to many elements from an ADT collection column.

The remove_elements function works much like the add_elements function. It uses the same ADT collections as the add_elements function.

The code for the remove_elements function is:

SQL> CREATE OR REPLACE
  2    FUNCTION remove_elements
  3    ( pv_list      LIST
  4    , pv_elements  LIST ) RETURN LIST IS
  5      /* Declare local return collection variable. */
  6      lv_list      LIST;
  7    BEGIN
  8      /* Check for instantiation and element membership. */
  9      IF NOT (pv_list IS NULL AND pv_elements IS NULL) AND
 10             (pv_list.COUNT > 0 AND pv_elements.COUNT > 0) THEN
 11        /* Assign parameters to local variables. */
 12        lv_list := pv_list;
 13        /* Remove any elements from a collection. */
 14        FOR i IN 1..lv_list.COUNT LOOP
 15          FOR j IN 1..pv_elements.COUNT LOOP
 16            IF lv_list(i) = pv_elements(j) THEN
 17              lv_list.DELETE(i);
 18              EXIT;
 19            END IF;
 20          END LOOP;
 21        END LOOP;
 22      END IF;
 23  
 24      /* Return modified collection. */
 25      RETURN lv_list;
 26    END remove_elements;
 27  /

Lines 3, 4, and 6 work like the add_elements function. Lines 9 and 10 differ because they check for initialized collections that hold at least one element each. Line 12 mimics the behavior of line 13 in the add_elements function. Lines 14 through 16 implements a nested loop and filtering IF-statement. The IF-statement checks for a valid element to remove from the ingredient ADT column’s list of values.

Line 17 removes an element from the list. Line 18 exits the inner loop to skip the evaluation of other non-matches. It’s possible to do this because the add_elements and change_elements functions ensure a unique list of string values in the ingredient ADT collection.

The test case for the remove_elements function works like the earlier tests. You query the row that you will update to check its values; for instance:

SQL> SELECT pizza_id, pizza_size
  2  ,      serialize_set(ingredients) AS ingredients
  3  FROM   pizza
  4  WHERE  customer_id =
  5          (SELECT customer_id FROM customer
  6           WHERE  first_name = 'Thea' AND last_name = 'Queen');

It should return:

Pizza Pizza
  ID # Size   Ingredients
------ ------ ----------------------------------------------------------------
     2 Medium Cheese, Marinara Sauce, Canadian Bacon, Linguica

You would remove an element from the ingredient ADT collection column with the following UPDATE statement:

SQL> UPDATE pizza
  2  SET    ingredients =
  3           remove_elements(ingredients,list('Canadian Bacon'))
  4  WHERE  customer_id =
  5          ( SELECT customer_id FROM customer
  6            WHERE  first_name = 'Thea' AND last_name = 'Queen' );

When you re-query the row, you should see that Canadian Bacon is no longer an element in the ingredient ADT collection column. Like this:

Pizza Pizza
  ID # Size   Ingredients
------ ------ ----------------------------------------------------------------
     2 Medium Cheese, Marinara Sauce, Linguica

This two article series has shown you the differences between working with ADT and UDT collection. It has also shown you how to create PL/SQL functions to enable you to add, change, and remove elements from ADT column inside an UPDATE statement.

The next step would be for you to put the serialize_set, add_elements, change_elements, and remove_elements functions into an adt package. That package would look like:

SQL> CREATE OR REPLACE
  2    PACKAGE adt IS
  3  
  4    FUNCTION add_elements
  5    ( pv_list     LIST
  6    , pv_element  LIST ) RETURN LIST;
  7  
  8    FUNCTION change_elements
  9    ( pv_list     LIST
 10    , pv_element  CHANGE ) RETURN LIST;
 11  
 12    FUNCTION remove_elements
 13    ( pv_list      LIST
 14    , pv_elements  LIST ) RETURN LIST;
 15  
 16    FUNCTION serialize_set
 17    (pv_list LIST) RETURN VARCHAR2;
 18  
 19  END adt;
 20  /

Beyond writing an ADT package to manage a list of variable length strings, you have the opportunity to extend behaviors further through overloading. Overloading lets you define functions that use the same name with different parameter lists.

For example, you could define the LIST_D, LIST_N, and LIST_S as SQL ADT where they would implement ADTs of dates, numbers, and strings respectively. Then, you would write three versions of the preceding four functions. Each set of functions would work with one of the type specific ADTs, and provide you with a powerful utility package to add, change, remove, and serialize the values of date, number, and string ADTs.

When you put all the related functions into a package you simplify access and organize for reusability. That way you have all the tools you need inside a single adt package to write advanced UPDATE statements against ADT nested tables.