Page 599
Listing 23.10. Qualifying an attribute with the name of the object column.
SQLWKS> select Location.Building, Location.Room from Seminar; LOCATION.BUILDING LOCATI -------------------- ------ NARROW HALL B200 1 row selected.
Oracle8 also supports the use of nested tables. Suppose you create an object datatype called Test_Score which is designed to store a student's ID and a score on a particular test (see Listing 23.11). You can also create another datatype called Test_Score_Table which is a table based on the Test_Score datatype. Finally, you can use the Test_Score_Table datatype to specify a column's datatype when you create or alter a table.
Listing 23.11. Creating a table that contains a nested table column.
SQLWKS> create type Test_Score as object (
2> Student_ID varchar2(6),
3> Score number);
Statement processed.
SQLWKS> create type Test_Score_Table as table of Test_Score;
Statement processed.
SQLWKS> create table Test_Results (
2> Instructor_ID varchar2(6),
3> Class_ID varchar2(6),
4> Test_Name varchar2(30),
5> Scores Test_Score_Table)
6> nested table Scores store as Test_Scores;
Statement processed.
ANALYSIS
The last SQL statement in Listing 23.11 is a CREATE TABLE statement. When you create a table that contains a column that is a nested table, you must specify a storage table that is identified for the nested table.
When you specify a set of values for a nested table in a DML statement, you must specify the constructor method for the nested table datatype, and, as arguments to that constructor method, you can specify the constructor method for the datatype on which the nested table datatype is based. Listing 23.12 presents an example; a single row is inserted in the Test_Results table. Even though a single row is inserted in the Test_Results table, three test scores are stored in the column Scores, which is a nested table.
Page 600
Listing 23.12. Inserting a row into a table which contains a nested table column.
SQLWKS> insert into Test_Results
2> (Instructor_ID, Class_ID, Test_Name, Scores)
3> values
4> (`E101', `123456', `Final exam',
5> Test_Score_Table
6> (Test_Score (`A12345', 98),
7> Test_Score (`E13111', 87),
8> Test_Score (`F13999', 84)));
1 row processed.
ANALYSIS
On line 5, the constructor method for the nested table type Test_Score_Table is specified. On lines 6, 7, and 8, the constructor method for Test_Score is specified with each set of attribute values.
A new datatype available in Oracle8 is the VARYING ARRAY or VARRAY. This datatype represents an ordered set of elements of the same datatype.
Here's a simplified version of the VARRAY syntax:
CREATE TYPE type_name as VARRAY (limit) of datatype;
The variables are defined as follows:
type_name is the name of the VARRAY type to be created.
limit is the maximum number of elements in the array.
datatype is either a predefined or user-defined datatype.
The VARRAY limit must be an integer literal; you cannot use an expression to specify the limit. As you can see in Listing 23.13, once a VARRAY type is declared, it can be used as the datatype for a column.
Listing 23.13. Creating a table that contains a VARRAY column.
SQL> create type Available_Rooms as varray (100) of Available_Room; Type created. SQL> create table Class_Scheduling ( 2 Administrator_ID varchar2(6), 3 Semester varchar2(6), 4 Year number, 5 Room_Assignments Available_Rooms); Table created.
Page 601
Let's look at how you specify values for a VARRAY column in an INSERT statement. If you want to specify a null value for the VARRAY column, you can specify the constructor method for the VARRAY datatype with a NULL argument (see Listing 23.14).
Listing 23.14. Specifying a null value for a VARRAY column in an INSERT.
SQL> insert into Class_Scheduling 2 (Administrator_ID, Semester, Year, Room_Assignments) 3 values 4 (`101', `FALL', `1998', Available_Rooms(NULL)); 1 row created.
If you want to specify a value for an object column in a DML statement, you must specify the constructor method for the base object type enclosed within the constructor method for the VARRAY type. For example, Listing 23.15 contains an INSERT statement that attempts to store an available room in the column Room_Assignments. Oracle rejects the first INSERT statement because Available_Rooms is a VARRAY. The second INSERT statement uses the constructor method for Available_Room enclosed within the constructor method for Available_Rooms.
Listing 23.15. Specifying a value for a VARRAY column in an INSERT.
SQL> insert into Class_Scheduling
2 (Administrator_ID, Semester, Year, Room_Assignments)
3 values
4 (`101', `FALL', `1998', Available_Rooms(`FLUGLE HALL', 100));
(`101', `FALL', `1998', Available_Rooms(`FLUGLE HALL', 100))
*
ERROR at line 4:
ORA-00932: inconsistent datatypes
SQL> insert into Class_Scheduling
2 (Administrator_ID, Semester, Year, Room_Assignments)
3 values
4 (`101', `FALL', `1998',
5 Available_Rooms(Available_Room(`FLUGLE_HALL', `100')));
1 row created.
Page 602
To help the user inspect user-defined types, Oracle8 includes some new data dictionary views:
Listing 23.16 contains a description of the columns returned by USER_TYPES and a sample query against USER_TYPES.
| NOTE |
Many of the Oracle data dictionary views are categorized as USER_, ALL_, and DBA_. Those views that begin with USER provide information on the applicable objects owned by the user who is querying that data dictionary view. Those views that begin with ALL provide information on all applicable objects that the user who is querying that data dictionary view has the privilege to see. Finally, those views that begin with DBA provide information on all applicable objects in the database. Although this section discusses only the USER_TYPES and USER_TYPE_ATTRS data dictionary views, the Oracle8 server also provides four other related data dictionary views: ALL_TYPES, ALL_TYPE_ATTRS, DBA_TYPES, and DBA_TYPE_ATTRS. |
Listing 23.16. Looking at the contents of USER_TYPES.
SQLWKS> desc USER_TYPES Column Name Null? Type ------------------------------ -------- ---- TYPE_NAME NOT NULL VARCHAR2(30) TYPE_OID NOT NULL RAW(16) TYPECODE VARCHAR2(30) ATTRIBUTES NUMBER METHODS NUMBER PREDEFINED VARCHAR2(3) INCOMPLETE VARCHAR2(3) SQLWKS> select type_name, typecode, attributes, methods from user_types; TYPE_NAME TYPECODE ATTRIBUTES METHODS ------------------------------ ------------------ ---------- ---------- AVAILABLE_ROOM OBJECT 2 0 AVAILABLE_ROOMS COLLECTION 0 0 CLASS_PRESIDENTS COLLECTION 0 0