Object-Relational Features - Beginning Oracle SQL: For Oracle Database 12c, Third Edition (2014)

Beginning Oracle SQL: For Oracle Database 12c, Third Edition (2014)

Chapter 12. Object-Relational Features

As promised in the introduction of this book, this final chapter discusses some object-relational features of the Oracle DBMS. For a proper understanding and appreciation of object-relational database features in general, you should consider those features in the context of an object-oriented development environment. Because this book is devoted to Oracle SQL, this chapter focuses on the consequences of these object-relational features for the SQL language.

The first step in setting up an object-relational environment is the definition of the appropriate collection of object types and methods. Once you have defined your object types, you can use them to create object tables, thus creating a truly object-relational environment. You can also useobject views to create an object-relational layer on top of standard relational environments. This chapter mainly uses object types as a starting point for creating user-defined datatypes and then uses those datatypes in relational table structures.

Along with “regular” user-defined datatypes, there are two special user-defined datatypes, also referred to as collection types because they are multivalued: variable arrays and nested tables. Sections 12.1–12.4 of this chapter cover collection types and user-defined datatypes.

Section 12.5 introduces the ANSI/ISO standard multiset operators, which allow you to perform various sophisticated operations with nested tables. Note that the PL/SQL language normally plays an important role in creating an object-relational environment. PL/SQL is the programming language you need in the definition phase of such an environment. Because PL/SQL is not covered in this book, we assume some basic knowledge of this language.

image Note Instead of PL/SQL, you can also use the Java language to create an object-relational environment.

12.1 More Datatypes

So far in this book, we have used only the standard, built-in datatypes supported by Oracle, such as NUMBER, BINARY_FLOAT, BINARY_DOUBLE, DATE, TIMESTAMP [WITH [LOCAL] TIMEZONE], INTERVAL, [N]CHAR, and [N]VARCHAR2. This means that we haven’t discussed the following two Oracle datatype categories:

· Collection datatypes: These are variable arrays (varrays) and nested tables. You are probably familiar with the concept of arrays from other programming languages, and nested tables are tables within a table.

· User-defined datatypes: These allow you (as the name indicates) to define your own complex datatypes.

Collection Datatypes

Collection datatypes are a special case of user-defined datatypes. Collection datatypes support attributes that can have multiple values. For example, you can store a list of phone numbers for each employee in a single column, or you can add a set of errata entries to every row in the COURSEStable.

The first example (adding a list of phone numbers) is an obvious candidate for using a varray, because, in general, you know the maximum length of such a list of phone numbers in advance. Also, you probably want to assign some meaning to the order of the phone numbers in the list (office extension, home, mobile, fax, and so on).

It is probably better to implement the second example (maintaining course errata) with a nested table, because you don’t have an idea beforehand about how many errata entries to expect.

image Note As you will see soon, you cannot create nested tables without using user-defined datatypes.

As a user-defined datatype, you might, for example, create an ADDRESS type, with STREET, NUMBER, POSTALCODE, and CITY components. You can create arrays of user-defined datatypes- and use ADDRESS type to add an array of addresses to the OFFERINGS table. That would allow you to store multiple alternative location addresses for course offerings. If you want to store only a single location address, you obviously don’t need an array—a regular user-defined address type would be sufficient.

Methods

You can add methods to user-defined datatypes. Methods are operations specifically developed to work with your user-defined datatypes; for example, to specify how you want to compare two address type values, or how you want to sort address values.

Methods add a lot of semantic power to your user-defined datatypes. Unfortunately we can’t spend much time on methods in this book, because you need a great deal of PL/SQL programming to create methods. If you want to see some method examples, check out the CUSTOMERS table of the OE schema, one of the standard sample schemas that ships with the Oracle software.

As you will see in the Section 12.2, as soon as you create a user-defined datatype in Oracle, you implicitly get one method “for free”—a method with the same name as the datatype itself. That method is the constructor method, which allows you to create occurrences of the datatype.

OBJECT-RELATIONAL VS. STANDARD RELATIONAL TECHNIQUES

For the examples mentioned so far in this chapter, you could argue that you could implement them very well with standard relational techniques, as discussed in previous chapters of this book. You could separate various phone numbers into separate columns, you could create a separateERRATA table with a foreign key constraint referring to the COURSES table, and so on.

So when should you choose an object-relational approach rather than a pure relational approach? It might be a matter of taste, and discussions about taste are probably a waste of time in a technical book like this one. As the Romans said, “De gustibus non disputandum est…” (That phrase translates to “There is no disputing about tastes.”)

It might be the case that you have a powerful object-oriented design and development environment. You may find that Oracle’s object-relational features enable you to maintain an intuitive and straightforward mapping between that development environment and the Oracle database structures.

In any case, this book does not speculate about when one approach is better than the other. The examples in this chapter have a single purpose: to illustrate the object-relational features of the Oracle DBMS.

As you read about the techniques described in this chapter, you may wonder whether they violate the first normal form as one of the foundations of the relational model. That is not the case. The relational model does not forbid in any way storing complex or set-valued attributes in your rows. Data “atomicity” is a rather slippery concept. For example, if you consider DATE values, aren’t you looking at a compound datatype? A DATE value has meaningful subcomponents, such as year, month, and day. For a thorough treatment of this subject, see An Introduction to Database Systems, 8th Edition by Chris Date (Addison-Wesley, 2003).

12.2 Varrays

We will begin to explore varrays by implementing the phone list example introduced in the previous section. To keep our EMPLOYEES table unimpaired, we create a copy of the EMPLOYEES table for our experiments in this final chapter of the book. We also leave out some of the columns of the original EMPLOYEES table. See Listing 12-1.

Listing 12-1. Creating a Copy of the EMPLOYEES Table

create table eas
select empno, ename, init, mgr, deptno
from employees;

Creating the Array

Before we can add a list of phone numbers for every employee in the E table, we must create a corresponding type first, as shown in Listing 12-2.

Listing 12-2. Creating and Describing a Type

create or replace type numberlist_t
as varray(4) of varchar2(20);
/

describe numberlist_t
numberlist_t VARRAY(4) OF VARCHAR2(20)

select type_name, typecode
from user_types;

TYPE_NAME TYPECODE
------------------------ ------------------------------
NUMBERLIST_T COLLECTION

Note that you must end the CREATE TYPE command in Listing 12-2 with a slash (/) in the third line, although you ended the second line with a semicolon. The reason is that you are not entering an SQL or an SQL*Plus command; you’re entering a PL/SQL command.

Note also that from now on, you can use this NUMBERLIST_T type as often as you like. It is known to the database, and its definition is stored in the data dictionary. You can query the USER_TYPES data dictionary view to see your own type definitions.

image Note To allow other database users to use your type definitions, you must grant them the EXECUTE privilege on those types.

In Listing 12-3, we add a column to the E table, using the NUMBERLIST_T type we created in Listing 12-2. Then, we execute a query.

Listing 12-3. Adding a Column Based on the NUMBERLIST_T Type

alter table e add (numlist numberlist_t);

describe e
Name Null? Type
------------ ------------- -------------
EMPNO NUMBER(4)
ENAME NOT NULL VARCHAR2(8)
INIT NOT NULL VARCHAR2(5)
MGR NUMBER(4)
DEPTNO NUMBER(2)
NUMLIST NUMBERLIST_T

select empno, numlist from e;
EMPNO NUMLIST
------- ----------------------------------------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

The query results are not impressive. Obviously, the new NUMLIST column is still empty. So we have the following two problems to solve:

· How can we populate the NUMLIST column with phone numbers?

· After the column has these phone numbers, how can we retrieve them?

Populating the Array with Values

As mentioned earlier in the chapter, each user-defined object type implicitly has a function of the same name, allowing you to generate or construct values of that object type. This function is normally referred to as the constructor method. In other words, if you create a user-defined object type, you get a constructor method for free, with the same name as the object type.

Listing 12-4 shows how you can assign phone number lists to five employees in the E table. Note that you can skip elements, if you like, and you can also assign empty number lists.

Listing 12-4. Assigning Values to the NUMLIST Column

update e
set numlist = numberlist_t('1234','06-78765432','029-8765432')
where empno = 7839;

update e
set numlist = numberlist_t('4231','06-12345678')
where empno = 7782;

update e
set numlist = numberlist_t('2345')
where empno = 7934;

update e
set numlist = numberlist_t('','06-23456789')
where empno = 7698;
update e
set numlist = numberlist_t()
where empno in (7566,7844);

Querying Array Columns

Now let’s see what happens if we select the NUMLIST column, without applying any functions or operators to that column. In that case, we simply get the values back the same way we inserted them, including the constructor method, as shown in Listing 12-5.

Listing 12-5. Querying the NUMLIST Column

select empno, numlist
from e
where empno in (7566,7698,77832,7839,7934);

EMPNO NUMLIST
-------- --------------------------------------------------
7566 NUMBERLIST_T()
7698 NUMBERLIST_T(NULL, '06-23456789')
7839 NUMBERLIST_T('1234', '06-78765432', '029-8765432')
7934 NUMBERLIST_T('2345')

If you want to select individual phone numbers from the NUMLIST array, you need to “un-nest” the phone numbers first. You can un-nest arrays with the TABLE function. Listing 12-6 shows how you can use the TABLE function for that purpose. (For further details about the TABLEfunction, see Oracle SQL Reference.)

Listing 12-6. Using the TABLE Function to Un-Nest the NUMLIST Array

break on empno

select e.empno, n.*
from e
, TABLE(e.numlist) n;

EMPNO COLUMN_VALUE
-------- ------------------
7698
06-23456789
7782 4231
06-12345678
7839 1234
06-78765432
029-8765432
7934 2345

Suppose that we want to go one step further and be able to select specific phone numbers from the array (for example, the second one). In that case, we need PL/SQL again, because the SQL language does not support a direct way to access array elements by their index value. It is not difficult to build a PL/SQL function to return a certain element from an array. Chapter 5 showed an example of a PL/SQL stored function to count the number of employees per department (Listing 5-31). Listing 12-7 shows how you can create a PL/SQL stored function to return the first phone number from the NUMLIST array, assuming that number represents the internal extension number.

Listing 12-7. Creating a PL/SQL Function to Return Array Elements

create or replace function ext
(p_varray_in numberlist_t)
return varchar2
is
v_ext varchar2(20);
begin
v_ext := p_varray_in(1);
return v_ext;
end;
/
Function created.

select ename, init, ext(numlist)
from e
where deptno = 10;

ENAME INIT EXT(NUMLIST)
-------- ----- ------------
CLARK AB 4231
KING CC 1234
MILLER TJA 2345

The DEPTNO value (10) in the WHERE clause of this query is carefully chosen in order to avoid error messages. Just change the DEPTNO value in Listing 12-7, and you will see the corresponding Oracle error messages.

image Note The EXT stored function is kept as simple as possible. For example, there is no code to handle situations where employees have no phone number list or an empty phone number list. It is relatively easy to enhance the EXT function definition with some proper exception handling. However, this is not a PL/SQL book, and the EXT function is meant only to illustrate the concept.

It is impossible to update specific elements of an array. You can only replace an entire array value with a new one.

12.3 Nested Tables

Nested tables offer you more flexibility than arrays. There are many similarities between arrays and nested tables. However, an important difference is that nested tables require one extra step. In the previous section, you saw that you create a type and then use it to define arrays. For nested tables, you first create a type, then you create a table type based on that type, and then you create a nested table based on that table type.

Creating Table Types

To demonstrate how to use nested tables we will implement the example of maintaining course errata, introduced in Section 12.1. Listing 12-8 shows how to create the two types we need for implementing the errata example as a nested table.

Listing 12-8. Creating a Table Type for a Nested Table

create or replace type erratum_t as object
( code varchar2(4)
, ch number(2)
, pg number(3)
, txt varchar2(40)
) ;
/

create or replace type errata_tab_t as table of erratum_t;
/

describe errata_tab_t
errata_tab_t TABLE OF ERRATUM_T
Name Null? Type
------------------------------- -------- ---------------
CODE VARCHAR2(4)
CH NUMBER(2)
PG NUMBER(3)
TXT VARCHAR2(40)

Creating the Nested Table

Listing 12-9 shows the next step of creating the nested table based on the ERRATA_TAB_T type. Just as we did in the previous section with the EMPLOYEES table, we first create a copy C of the COURSES table to keep that table unimpaired.

Listing 12-9. Creating a Table with a Nested Table Column

create table c
as
select * from courses;

alter table c
add (errata errata_tab_t)
nested table errata store as errata_tab;

update c
set errata = errata_tab_t();

In Listing 12-9, the ALTER TABLE command adds an ERRATA nested table column to the C table, and the UPDATE command assigns an empty nested table to the ERRATA column for every row. Note that we use the ERRATA_TAB_T table type constructor method for that purpose.

Populating the Nested Table

Now we can add rows to the nested table, as shown in Listing 12-10. Note that you can access nested tables only within the context of the table they are part of; it is impossible to access them as independent tables. Listing 12-10 uses the TABLE function again, just as we did before in Listing 12-6, to un-nest the nested table.

Listing 12-10. Inserting Rows into the Nested Table

insert into table ( select errata
from c
where code = 'SQL')
values ('SQL'
, 3
, 45
, 'Typo in last line.');

We inserted an erratum entry for the SQL course, Chapter 3, page 45. In a similar way, you can also delete rows from a nested table. As stated in the introduction to this section, nested tables offer more flexibility than arrays. For example, you can update individual column values of a nested table, whereas you can only replace arrays in their entirety.

Suppose we made a typo in Listing 12-10 while entering the chapter number: the erratum was not in Chapter 3, but rather in Chapter 7. Listing 12-11 shows how we can correct this mistake with an UPDATE command. Note that line 3 introduces tuple variable e ranging over the result of the TABLE function, allowing us to use that tuple variable on the fourth line to refer to its chapter (CH) column value.

Listing 12-11. Updating Individual Columns of Nested Tables

update table ( select errata
from c
where code = 'SQL') e
set e.ch = 7;

Querying the Nested Table

If you want to retrieve all errata entries for the SQL course, you can join the course’s table (C) with its nested table, as shown in Listing 12-12.

Listing 12-12. Selecting Errata for the SQL Course

select code
, c.description
, e.ch, e.pg, e.txt
from c
join
table(c.errata) e
using (code);

CODE DESCRIPTION
------- ------------------------------
CH PG TXT
--- --- ------------------------------
SQL Introduction to SQL
7 45 Typo in last line.

As Listing 12-12 shows, this nested table join syntax is very similar to the syntax you use for regular joins (discussed in Chapter 8). The TABLE function un-nests its column-valued argument (c.errata) into a table.

Note that you can only refer to c.ERRATA because you specify the C table first in the FROM clause. The FROM clause order is important in this case. If you swap the two table expressions, you get the following Oracle error message:

select code
, c.description
, e.ch, e.pg, e.txt
from table(c.errata) e
join
c
using (code);
from table(c.errata) e
*
ERROR at line 4:
ORA-00904: "C"."ERRATA": invalid identifier

Listing 12-12 shows only a single row, because we inserted only a single erratum into the nested table. The last section of this chapter revisits nested tables, showing how you can use multiset operators on nested tables. These multiset operators could be a reason to consider using nested tables instead of regular (relational) tables with primary key and foreign key constraints. The multiset operators allow you to write elegant SQL statements that would need quite complicated syntax without them.

One additional thought: a nested table is a table like any other, and as the volume of data increases, it might help the performance of SQL statements against the nested table if the column used in the WHERE clause for searching were indexed.

So in summary, varrays and nested tables present similar solutions to the requirement for a complex column containing many elements. Varrays provide a faster solution needing only type definitions, but with restrictions such as only one column and a limit to the number of elements and fixed positions for the elements. Nested tables require more consideration involved in creating an actual table with its own storage requirements, but the rows in a nested table have no restrictions on quantity and they can be sorted any way on retrieval. Also, varrays can only be modified by writing a PL/SQL procedure, whereas nested tables can be accessed and updated more intuitively using SQL statements.

12.4 User-Defined Types

Your application may require a special, complex datatype. In that case, you would create a user-defined type.

Creating User-Defined Types

The third example mentioned in Section 12.1 was the compound ADDRESS type, used to store addresses with meaningful subcomponents into a single column. Listing 12-13 shows how you can create such a type.

Listing 12-13. Creating and Using User-Defined Types

create type address_t as object
( street varchar2(20)
, nr varchar2(5)
, pcode varchar2(6)
, city varchar2(20)
) ;
/

describe address_t

Name Null? Type
---------------------------------- -------- --------------
STEET VARCHAR2(20)
NR VARCHAR2(5)
PCODE VARCHAR2(6)
CITY VARCHAR2(20)

select type_name, typecode
from user_types;

TYPE_NAME TYPECODE
------------------------------ ------------------
NUMBERLIST_T COLLECTION
ERRATUM_T OBJECT
ERRATA_TAB_T COLLECTION
ADDRESS_T OBJECT

create table o
as
select course, begindate, trainer
from offerings;

alter table o add (address address_t);

update o
set o.address =
address_t('','','',
(select initcap(x.location)
from offerings x
where x.course = o.course
and x.begindate = o.begindate)
)
;

Note that we now have four user-defined types, as shown by the query against the USER_TYPES data dictionary view. Then we create a copy O of the OFFERINGS table (again, to keep the original table unimpaired) and add an ADDRESS column to the O table. As a last step, Listing 12-13 updates the O table with some address values. The last command uses the ADDRESS_T function to generate address values, leaving the first three address fields empty and selecting the city name from the original OFFERINGS table with a subquery.

Showing More Information with DESCRIBE

If you use user-defined datatypes, you can change the behavior of the SQL*Plus DESCRIBE command to show more information by setting its DEPTH attribute to a value higher than 1 or to ALL. See Listing 12-14 for an example.

Listing 12-14. Setting the DEPTH Attribute of the DESCRIBE Command

describe o
Name Null? Type
----------------- -------- ------------
COURSE NOT NULL VARCHAR2(4)
BEGINDATE NOT NULL DATE
TRAINER NUMBER(4)
ADDRESS ADDRESS_T

set describe depth 2
describe o
Name Null? Type
----------------- -------- ----------------
COURSE NOT NULL VARCHAR2(4)
BEGINDATE NOT NULL DATE
TRAINER NUMBER(4)
ADDRESS ADDRESS_T
STREET VARCHAR2(20)
NR VARCHAR2(5)
PCODE VARCHAR2(6)
CITY VARCHAR2(20)

The DESCRIBE command now also shows the subcomponents of your user-defined types. If your object-relational tables have additional method functions, they are shown as well.

12.5 Multiset Operators

This section discusses the ANSI/ISO standard multiset operators of the SQL language. We will first look at a complete list of all SQL multiset operators with a brief description. You can use these operators only on nested tables. Therefore, to allow for some multiset operator examples in this section, we will enter some more nested table entries in the ERRATA nested table. You will also see how you can convert arrays into nested tables “on the fly,” using the CAST and COLLECT functions.

Which SQL Multiset Operators Are Available?

If you are using nested tables in your table design, you can apply various SQL multiset operators against those tables. Multiset operators allow you to compare nested tables, check certain nested table properties, or derive new nested tables from existing ones.

image Note The SQL language refers to multisets to indicate a rather important difference between these sets and “regular” sets. In mathematics, duplicate elements in sets are meaningless. In SQL, multisets may have meaningful duplicates; that is, you cannot ignore duplicates in multisets.

Table 12-1shows an overview of the Oracle multiset operators. Note that these multiset operators are also part of the ANSI/ISO SQL standard. For completeness, Table 12-1 not only shows the SQL multiset operators, but also some other operations you can apply to nested tables.

Table 12-1. SQL Multiset Operators and Functions

Multiset Operator or Function

Description

nt1 MULTISET EXCEPT[DISTINCT] nt2

The difference of nt1 and nt2 (equivalent with the MINUS set operator)

nt1 MULTISET INTERSECT[DISTINCT] nt2

The intersection of nt1 and nt2

nt1 MULTISET UNION[DISTINCT] nt2

The union of nt1 and nt2

CARDINALITY(nt)

The number of rows in nt

nt IS [NOT] EMPTY

Boolean function to check whether nt is empty

nt IS [NOT] A SET

Boolean function to check whether nt contains duplicates

SET(nt)

Removes duplicates from nt

nt1 = nt2

Checks whether nt1 and nt2 are equal

nt1 IN(nt2, nt3, ...)

Checks whether nt1 occurs in a list of nested tables

nt1 [NOT] SUBMULTISETOF nt2

Checks whether nt1 a subset of nt2

r [NOT] MEMBEROF nt

Checks whether row r occurs in table nt

CAST(COLLECT(col))

Produces a nested table based on column col

POWERMULTISET(nt)

The set of all nonempty subsets of nt

POWERMULTISET_BY_CARDINALITY(nt,c)

The set of all nonempty subsets of nt with cardinality c

The following sections show a few typical examples of using multiset operators and functions. See the Oracle SQL Reference documentation for examples of all these operators and functions.

Preparing for the Examples

In Section 12.3, you learned how you can store errata entries for courses in a nested table. In Listing 12-10, we inserted only a single erratum. In Listing 12-15, we insert some more rows into the ERRATA nested table.

Listing 12-15. Inserting Some More Errata Rows

insert into table ( select errata
from c
where code = 'SQL' )
values ('SQL'
, 3
, 46
,'Layout illustration' );

insert into table ( select errata
from c
where code = 'SQL' )
values ('SQL'
, 5
, 1
,'Introduction missing.' );

insert into table ( select errata
from c
where code = 'XML' )
values ('XML'
, 5
, 1
, 'Introduction missing.' );

insert into table ( select errata
from c
where code = 'XML' )
values ('XML'
, 7
, 3
,'Line 5: "succeeds" should read "fails"' );

Now we have five errata entries in total: three for the SQL course and two for the XML course. If you execute a “regular” query against the C table and select its ERRATA column without using any modifying functions, the structure of the ERRATA column (with the nested table) becomes clear from the query result, as shown in Listing 12-16.

Listing 12-16. Querying a Nested Table Without Using Modifying Functions

col errata format a80 word

select errata
from c
where code = 'SQL';

ERRATA(CODE, CH, PG, TXT)
----------------------------------------------------------------------
ERRATA_TAB_T(ERRATUM_T('SQL', 7, 45, 'Typo in last line.'),
ERRATUM_T('SQL', 3, 46, 'Layout illustration'),
ERRATUM_T('SQL', 5, 1, 'Introduction missing.'))

image Note The query output in Listing 12-16 is formatted for readability.

The query result in Listing 12-16 consists of only a single row with a single column. In other words, you are looking at a complicated but single value. If you interpret that single value “inside out,” you see that the ERRATUM_T constructor function (or method) appears three times to build individual erratum entries. These three erratum entries, in turn, are elements in a comma-separated list. The ERRATA_TAB_T constructor function takes that comma-separated errata list as an argument to convert it into a nested table.

Using IS NOT EMPTY and CARDINALITY

Listing 12-17 uses the IS NOT EMPTY operator to select only those courses that have at least one erratum entry, and it uses the CARDINALITY function to show the number of errata for those courses.

Listing 12-17. IS NOT EMPTY and CARDINALITY Example

select code, cardinality(errata)
from c
where errata is not empty;

CODE CARDINALITY(ERRATA)
------- -------------------
SQL 3
XML 2

A corresponding query against a “regular” relational errata table would need a COUNT(*), a GROUP BY, and a HAVING clause.

Using POWERMULTISET

Listing 12-18 shows how you can produce the powermultiset of the ERRATA column for the SQL course. To increase the readability of the results in Listing 12-18, we issue a SQL*Plus BREAK command, which highlights the fact that the query result contains seven rows. Every row is a subset of the ERRATA nested table for the SQL course.

Listing 12-18. POWERMULTISET Example

break on row page

select *
from table ( select powermultiset(errata)
from c
where code = 'SQL' );

ERRATA_TAB_T(ERRATUM_T('SQL', 7, 45, 'Typo in last line.'))
ERRATA_TAB_T(ERRATUM_T('SQL', 3, 46, 'Layout illustration'))
ERRATA_TAB_T(ERRATUM_T('SQL', 7, 45, 'Typo in last line.'),
ERRATUM_T('SQL', 3, 46, 'Layout illustration'))
ERRATA_TAB_T(ERRATUM_T('SQL', 5, 1, 'Introduction missing.')
ERRATA_TAB_T(ERRATUM_T('SQL', 7, 45, 'Typo in last line.'),
ERRATUM_T('SQL', 5, 1, 'Introduction missing.'))
ERRATA_TAB_T(ERRATUM_T('SQL', 3, 46, 'Layout illustration'),
ERRATUM_T('SQL', 5, 1, 'Introduction missing.'))
ERRATA_TAB_T(ERRATUM_T('SQL', 7, 45, 'Typo in last line.'),
ERRATUM_T('SQL', 3, 46, 'Layout illustration'),
ERRATUM_T('SQL', 5, 1, 'Introduction missing.'))

image Note In mathematics, the powerset of a set X is the set consisting of all possible subsets of X.

The result contains seven rows because we have three SQL errata; see also Listing 12-17. Why seven rows for three errata? Well, there are the following possible subsets:

· Three possible subsets with cardinality 1 (rows 1, 2, and 4)

· Three possible subsets with cardinality 2 (rows 3, 5, and 6)

· One possible subset with cardinality 3 (row 7; that is, the nested table itself)

In mathematics, we would also expect the empty set to show up as an element of the powerset. However, the definition of the POWERMULTISET operator (see Table 12-1) explicitly excludes that subset, by stating that only nonempty subsets are considered.

Using MULTISET UNION

Listing 12-19 shows how you can use the MULTISET UNION operator to merge two nested tables into a single one. The query result is manually formatted to enhance readability, allowing you to see that the result is a single nested table, containing five errata entries. Without manual formatting, the query result will show up as one unstructured string.

Listing 12-19. MULTISET UNION Example

select c1.errata
MULTISET UNION
c2.errata
as result
from c c1,
c c2
where c1.code = 'SQL'
and c2.code = 'XML';

RESULT(CODE, CH, PG, TXT)
------------------------------------------------------------------------------
ERRATA_TAB_T( ERRATUM_T('SQL', 7, 45, 'Typo in last line.')
, ERRATUM_T('SQL', 3, 46, 'Layout illustration')
, ERRATUM_T('SQL', 5, 1, 'Introduction missing.')
, ERRATUM_T('XML', 5, 1, 'Introduction missing.')
, ERRATUM_T('XML', 7, 3, 'Line 5: "succeeds" should read "fails"')
)

Converting Arrays into Nested Tables

For the last example, we revisit the E table with the phone number array (see Listings 12-1 through 12-6). Listing 12-20 shows how you can use the COLLECT and CAST operators to convert an array into a nested table. To be able to capture the result, we first create a new numBer_tab_ttype using the existing numBerlist_t type.

Listing 12-20. CAST and COLLECT Example to Convert an Array into a Nested Table

create type number_tab_t
as table of numberlist_t;
/

select cast(collect(numlist) as number_tab_t) as result
from e
where empno in (7839, 7782);

RESULT
----------------------------------------------------------------------
NUMBER_TAB_T(NUMBERLIST_T('4231', '06-12345678'),
NUMBERLIST_T('1234', '06-78765432', '029-8765432'))

This final chapter gave you a high-level introduction to the object-relational features of the Oracle DBMS, focusing on the way you can use those features in SQL. You learned how you can create object types, and how you can use those types as user-defined datatypes. You also learned about the Oracle collection types: variable arrays and nested tables. If your tables contain nested tables, you can use SQL multiset operators on those tables.

If you want to learn more about the object-relational features of Oracle, refer to the Oracle documentation. Application Developer’s Guide: Object-Relational Features is an excellent starting point for further study in this area.

12.6 Exercises

You can do the following exercises to practice using the object-relational techniques covered in this chapter. The answers are in Appendix B.

1. The SALGRADES table has two columns to indicate salary ranges: LOWERLIMIT and UPPERLIMIT. Define your own SALRANGE_T type, based on a varray of two NUMBER(6,2) values, and use it to create an alternative SALGRADES2 table.

2. Fill the new SALGRADES2 table with a single INSERT statement, using the existing SALGRADES table.

3. Create a table TESTNEST with two columns: column X and column MX. Column X is NUMBER(1,0) with values 2, 3, 4, …, 9. Column MX is a nested table, based on a MX_TAB_T type, containing all multiples of X less than or equal to 20.

4. Use multiset operators to solve the following problems, using the TESTNEST table you created and populated in the previous exercise:

a. Which rows have a nested table containing value 12?

b. Which nested tables are not a subset of any other subset?

c. Which nested tables have more than 42 different nonempty subsets?