MacLochlainns Weblog

This two-part series covers how you update User-Defined Types (UDTs) and Attribute Data Types (ADTs). There are two varieties of UDTs. One is a column of a UDT object type and the other a UDT collection of a UDT object type.

You update nested UDT columns by leveraging the TABLE function. The TABLE function lets you create a result set, and access a UDT object or collection column. You need to combine the TABLE function and a CROSS JOIN to update elements of a UDT collection column.

ADTs are collections of a scalar data types. Oracle’s scalar data types are DATE, NUMBER, CHAR and VARCHAR2 (or, variable length strings). ADTs are unique and from some developer’s perspective difficult to work with.

The first article in this series shows you how to work with a UDT object type column and a UDT collection type. The second article will show you how to work with an ADT collection type.

PL/SQL uses ADT collections all the time. PL/SQL also uses User-Defined Types (UDTs) collections all the time. UDTs can be record or object types, or collections of records and objects. Record types are limited, and only work inside a PL/SQL scope. Object types are less limited and you can use them in a SQL or PL/SQL scope.

Object types come in two flavors. One acts as a typical record structure and has no methods and the other acts like an object type in any object-oriented programming language (OOPL). This article refers only to object types like typical record structures. That means when you read ADTs you should think of a SQL collection of a scalar data type, and when you read UDTs you should think of a SQL collection of an object type without methods.

You can create tables that hold nested tables. Nested tables can use a SQL ADT or UDT data type. Inserting data into nested tables is straightforward when you understand the syntax, but updating nested tables can be complex. The complexity exists because Oracle treats nested tables of ADTs differently than UDTs. My article series will show you how to simplify updating ADT columns.

That’s why it has two parts:

• How you insert and update rows with UDT columns and collection columns
• How you insert and update rows with ADT collection columns

If you’re asking yourself why there isn’t a section for deleting rows, that’s simple. You delete them the same way as you would any other row, using the DELETE statement.

How you insert and update rows with UDT columns and collection columns

This section shows you how to create a table with a UDT column and a UDT collection column. It also shows you how to insert and update the embedded columns.

You insert into any ordinary UDT column by prefacing the data with a constructor name. A constructor name is the same as a UDT name. The following creates an address_type UDT that you will use inside a customer table:

SQL> CREATE OR REPLACE
  2    TYPE address_type IS OBJECT
  3    ( street  VARCHAR2(20)
  4    , city    VARCHAR2(30)
  5    , state   VARCHAR2(2)
  6    , zip     VARCHAR2(5));
  7  /

You should take note that the address_type UDT doesn’t have any methods. All object types without methods have a default constructor. The default constructor follows the same rules as tables in the database.

Create the sample customer table with an address column that uses the address_type UDT as its data type; for instance:

SQL> CREATE TABLE customer
  2  ( customer_id  NUMBER
  3  , first_name   VARCHAR2(20)
  4  , last_name    VARCHAR2(20)
  5  , address      ADDRESS_TYPE
  6  , CONSTRAINT pk_customer PRIMARY KEY (customer_id));

Line 5 defines the address column with the address_type UDT. You insert a row with an embedded address_type data record as follows:

SQL> INSERT
  2  INTO   customer
  3  VALUES
  4  ( customer_s.NEXTVAL
  5  ,'Oliver'
  6  ,'Queen'
  7  , address_type( street => '1 Park Place'
  8                , city   => 'Starling City'
  9                , state  => 'NY'
 10                , zip    => '10001'));

Lines 7 through 10 includes the constructor call to the address_type UDT. The address_type constructor uses named notation rather than positional notation. You should always try to use named notation for object type constructor calls.

Updating an element of a UDT object structure is straightforward, because you simply refer to the column and a member of the UDT object structure. The syntax for that type of UPDATE statement follows:

SQL> UPDATE customer c
  2  SET    c.address.state = 'NJ'
  3  WHERE  c.first_name = 'Oliver'
  4  AND    c.last_name = 'Queen';

The address_type UDT works for an object structure but not for a UDT collection. You need to add a column to differentiate between rows of the nested collection. You can redefine the address_type UDT as follows:

SQL> CREATE OR REPLACE
  2    TYPE address_type IS OBJECT
  3    ( status  VARCHAR2(8)
  4    , street  VARCHAR2(20)
  5    , city    VARCHAR2(30)
  6    , state   VARCHAR2(2)
  7    , zip     VARCHAR2(5));
  8  /

After creating the UDT object type, you need to create an address_table UDT collection of the address_type UDT object type. You use the following syntax to create the SQL collection:

SQL> CREATE OR REPLACE
  2    TYPE address_table IS TABLE OF address_type;
  3  /

Having both the UDT object and collection types, you can drop and create the customer table with the following syntax:

SQL> CREATE TABLE customer
  2  ( customer_id  NUMBER
  3  , first_name   VARCHAR2(20)
  4  , last_name    VARCHAR2(20)
  5  , address      ADDRESS_TABLE
  6  , CONSTRAINT pk_customer PRIMARY KEY (customer_id))
  7  NESTED TABLE address STORE AS address_tab;

Line 5 defines the address column as a UDT collection. Line 7 instructs how to store the UDT collection as a nested table. You designate the address column as the nested table and store it as an address_tab table. You can access the nested table only through its container, which is the customer table.

You can insert rows into the customer table with the following syntax. This example stores a single row with two elements of the address_type in the nested table:

SQL> INSERT
  2  INTO   customer
  3  VALUES
  4  ( customer_s.NEXTVAL
  5  ,'Oliver'
  6  ,'Queen'
  7  , address_table(
  8        address_type( status   => 'Obsolete'
  9                    , street => '1 Park Place'
 10                    , city => 'Starling City'
 11                    , state => 'NY'
 12                    , zip => '10001')
 13      , address_type( status   => 'Current'
 14                    , street => '1 Dockland Street'
 15                    , city => 'Starling City'
  16                    , state => 'NY'
 17                    , zip => '10001')));

Lines 7 through 17 have two constructor calls for the address_type UDT object type inside the address_table UDT collection. After you insert an address_table UDT collection, you can query an element by using the SQL built-in TABLE function and a CROSS JOIN. The TABLE function returns a SQL result set. The CROSS JOIN lets you create cross product that you can filter inside the WHERE clause.

A CROSS JOIN between two tables or a table and result set from a nested table matches every row in the customer table with every row in the nested table. A best practice would include a WHERE clause that filters the nested table to a single row in the result set.

The syntax for such a query is complex, and follows below:

SQL> COL first_name  FORMAT A8  HEADING "First|Name"
SQL> COL last_name   FORMAT A8  HEADING "Last|Name"
SQL> COL street      FORMAT A20 HEADING "Street"
SQL> COL city        FORMAT A14 HEADING "City"
SQL> COL state       FORMAT A5  HEADING "State"
SQL> SELECT c.first_name
  2  ,      c.last_name
  3  ,      a.street
  4  ,      a.city
  5  ,      a.state
  6  FROM   customer c CROSS JOIN TABLE(c.address) a
  7  WHERE  a.status = 'Current';

As mentioned, the TABLE function on line 6 translates the UDT collection into a SQL result set, which acts as a temporary table. The alias a becomes the name of the temporary table. Lines 3, 4, 5, and 7 all reference the temporary table.

The query should return the following for the customer and their current address value:

First    Last
Name     Name     Street               City           State
-------- -------- -------------------- -------------- -----
Oliver   Queen    1 Dockland Street    Starling City  NY

Oracle thought through the fact that you should be able to update UDT collections. The same TABLE function lets you update elements in the nested table. You can update the elements in nested UDT tables provided you create a unique key, such as a natural key or primary key. Oracle’s syntax doesn’t support constraints on nested tables, which means you need to implement it by design and protect by carefully controlling inserts and updates to the nested table.

You can update the state value of the current address with the following UPDATE statement:

SQL> UPDATE TABLE(SELECT c.address
  2               FROM   customer c
  3               WHERE  c.first_name = 'Oliver'
  4               AND    c.last_name = 'Queen') a
  5  SET    a.state = 'NJ'
  6  WHERE  a.status = 'Current';

Line 5 sets the current state value in the address_table UDT nested table. Line 6 filters the nested table to the current address element. You need to ensure that any UDT object type holds a member attribute or set of member attributes that holds a unique value. That’s because you need to ensure that there’s a way to find a unique element within a UDT collection. If you require the table, you should see the change inside the nested table.

Oracle does not provide equivalent syntax for such a change in an ADT collection type. The second article in this series show you how to implement PL/SQL functions to solve that problem.