The previous chapters in this book discuss topics that you need to get started with Oracle objects. The topics in this chapter are of interest once you start applying object-relational techniques to large-scale applications or complex schemas.
The chapter contains these topics:
Oracle database automatically maps the complex structure of object types into simple table structure for storage.
This section discusses these related topics:
An object type is like a tree structure, where the branches represent the attributes. Attributes that are objects sprout subbranches with their own attributes.
Ultimately, each branch ends at an attribute that is a built-in type; such as
REF, or a collection type, such as
VARRAY or nested table. Each of these leaf-level attributes of the original object type is stored in a table column.
Leaf-level attributes that are not collection types are called the leaf-level scalar attributes of the object type.
The following topics relate to the discussion of object tables and relational tables in "How Objects are Stored in Tables".
In an object table, Oracle database stores the data for every leaf-level scalar or
REF attribute in a separate column.
VARRAYis also stored in a column, unless it is too large. Oracle database stores leaf-level attributes of nested table types in separate tables associated with the object table. You must declare these tables as part of the object table declaration. See "Internal Layout of VARRAYs" and "Internal Layout of Nested Tables".
When you retrieve or change attributes of row objects in an object table, the database performs the corresponding operations on the columns of the table. Accessing the value of the row object itself invokes the default constructor for the type, using the columns of the object table as arguments and produces a copy of the object.
The database stores the system-generated object identifier in a hidden column. The database uses the object identifier to construct
REFs to the object.
When a table (relational table) is defined with a column of an object type, the database adds hidden columns to the table for the leaf-level attributes of the object type. Each object-type column also has a corresponding hidden column to store the NULL information for the column objects (that is, the atomic nulls of the top-level and the nested objects).
A substitutable column or object table has a hidden column not only for each attribute of the object type of the column but also for each attribute added in any subtype of the object type. These columns store the values of those attributes for any subtype instances inserted in the substitutable column.
Besides the type-discriminant column and the null-image column, the following are associated with a substitutable column of
person_typ, created by Example 8-1
A hidden column for each of the attributes of
Hidden columns for attributes of the subtypes of
Thus, the following might be associated with a substitutable column of
person_typ: the attributes
When you create a subtype, the database automatically adds hidden columns for new attributes in the subtype to tables containing a substitutable column of any of the ancestor types of the new subtype. These retrofit the tables to store data of the new type. If, for some reason, the columns cannot be added, creation of the subtype is rolled back.
When you drop a subtype using
DROP TYPE with the
VALIDATE option, the database automatically drops hidden columns for attributes unique to the subtype that do not contain data. Errors are raised if these columns contain data.
Example 8-1 creates types needed for subsequent examples in this chapter
-- drop any of these objects created for Ex.7-10 CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20), MAP MEMBER FUNCTION get_idno RETURN NUMBER ) NOT FINAL; / CREATE TYPE BODY person_typ AS MAP MEMBER FUNCTION get_idno RETURN NUMBER IS BEGIN RETURN idno; END; END; / CREATE TYPE student_typ UNDER person_typ ( dept_id NUMBER, major VARCHAR2(30)) NOT FINAL; / CREATE TYPE part_time_student_typ UNDER student_typ ( number_hours NUMBER); / CREATE TYPE employee_typ UNDER person_typ ( emp_id NUMBER, mgr VARCHAR2(30)); / CREATE TABLE person_obj_table OF person_typ; // an object table INSERT INTO person_obj_table VALUES (person_typ(12, 'Bob Jones', '650-555-0130')); INSERT INTO person_obj_table VALUES (student_typ(51, 'Joe Lane', '1-650-555-0140', 12, 'HISTORY')); INSERT INTO person_obj_table VALUES (part_time_student_typ(52, 'Kim Patel', '1-650-555-0135', 14, 'PHYSICS', 20));
Substitutable columns are associated with hidden type-discriminant columns. The hidden columns contains an identifier, called a typeid, that identifies the most specific type of each object in the substitutable columns. Typically, a typeid (
RAW) is one byte, though it can be as big as four bytes for a large hierarchy.
You can find the typeid of a specified object instance using the function
-- Requires Ex. 8-1 SELECT name, SYS_TYPEID(VALUE(p)) typeid FROM person_obj_table p;
Bob Jones 01
Joe Lane 02
Kim Patel 03
The catalog views
ALL_TYPES contain a
TYPEID column (not hidden) that gives the typeid value for each type. You can join on this column to get the type names corresponding to the typeids in a type-discriminant column.
See Also:"SYS_TYPEID" for more information about
SYS_TYPEID, typeids, and type-discriminant columns.
When the database constructs a
REF to a row object, the constructed
REF is made up of the object identifier (OID), some metadata of the object table, and, optionally, the
The size of a
REF in a column of
REF type depends on the storage requirements associated with the column, as follows:
If the column is declared as a
ROWID, the database stores the
ROWID in the
REF column. The
ROWID hint is ignored for object references in constrained
If a column is declared as a
REF with a
SCOPE clause, the column decreases due to the omission of the object table metadata and the
ROWID. A scoped
REF is 16 bytes long.
If the object identifier is primary-key based, the database may create one or more internal columns to store the values of the primary key, depending on how many columns comprise the primary key.
REFcolumn references row objects whose object identifiers are derived from primary keys, it is referred to as a primary-key-based
pkREF. Columns containing
pkREFs must be scoped or have a referential constraint.
The rows of a nested table are stored in a separate storage table. Each nested table column has a single associated storage table. The storage table holds all the elements for all of the nested tables in that column. The storage table has a hidden
NESTED_TABLE_ID column with a system-generated value that lets Oracle database map the nested table elements back to the appropriate row.
You can speed up queries that retrieve entire collections by making the storage table index-organized. Include the
ORGANIZATION INDEX clause inside the
STORE AS clause.
A nested table type can contain objects or scalars:
If the elements are objects, the storage table is like an object table: the top-level attributes of the object type become the columns of the storage table. However, you cannot construct
REFs to objects in a nested table because a nested table row has no object identifier column.
If the elements are scalars, the storage table contains a single column called
COLUMN_VALUE that contains the scalar values.
All the elements of a
VARRAY are stored in a single column. Depending upon the size of the array, it may be stored inline or in a
BLOB. See Storage Considerations for Varrays for details.
This section discusses the use of indexes on typeids and attributes.
This section contains the following topics:
SYS_TYPEID function, you can build an index on the hidden type-discriminant column of substitutable columns. The type-discriminant column contains typeids that identify the most specific type of every object instance stored in the substitutable column. The system uses this information to evaluate queries that filter by type using the
IS OF predicate, but you can access the typeids for your own purposes using the
Generally, a type-discriminant column contains only a small number of distinct typeids: at most, there can be only as many as there are types in the related type hierarchy. The low cardinality of this column makes it a good candidate for a bitmap index.
For example, the following statement creates a bitmap index on the type-discriminant column underlying the substitutable
contact column of table
contacts. The function
SYS_TYPEID references the type-discriminant column:
-- Requires Ex. 8-1 CREATE TABLE contacts ( contact person_typ, contact_date DATE ); INSERT INTO contacts VALUES ( person_typ (65,'Vrinda Mills', '1-650-555-0125'),'24 Jun 2003' ); INSERT INTO contacts VALUES ( person_typ (12, 'Bob Jones', '650-555-0130'),'24 Jun 2003' ); INSERT INTO contacts VALUES ( student_typ(51, 'Joe Lane', '1-650-555-0140', 12, 'HISTORY'),'24 Jun 2003' ); INSERT INTO contacts VALUES ( part_time_student_typ(52, 'Kim Patel', '1-650-555-0135', 14, 'PHYSICS', 20),'24 Jun 2003' ); CREATE BITMAP INDEX typeid_idx ON contacts (SYS_TYPEID(contact));
You can build an index on attributes for any types that can be stored in a substitutable column. You can reference attributes of subtypes in the
CREATE INDEX statement by filtering out types other than the desired subtype (and its subtypes) using the
TREAT function; you then use dot notation to specify the desired attribute.
For example, the following statement creates an index on the
major attribute of all students in the
contacts table. The declared type of the
contact column is
person_typ, of which
student_typ is a subtype, so the column may contain instances of
student_typ, and subtypes of either one:
-- Requires Ex.8-1- and 8-3 CREATE INDEX major1_idx ON contacts (TREAT(contact AS student_typ).major);
student_typ type first defined the
major attribute: the
person_typ supertype does not have it. Consequently, all the values in the hidden column for the
major attribute are values for persons of type
student_typ subtype). This means that the values of the hidden column are identical to the values returned by the
major values for all students, including student subtypes: both the hidden column and the
TREAT expression list majors for students and nulls for non-students. The system exploits this fact and creates index
major1_idx as an ordinary B-tree index on the hidden column.
Values in a hidden column are only identical to the values returned by the
TREAT expression just described if the type named as the target of the
TREAT function (
student_typ) is the type that first defined the
major attribute. If the target of the
TREAT function is a subtype that merely inherited the attribute, as in the following example, the
TREAT expression returns non-null
major values for the subtype (part-time students) but not for its supertype (other students).
CREATE INDEX major2_idx ON contacts (TREAT(contact AS part_time_student_typ).major);
Here, the values stored in the hidden column for
major may be different from the results of the
TREAT expression. Consequently, an ordinary B-tree index cannot be created on the underlying column. Therefore, the database treats the
TREAT expression like any other function-based expression and tries to create the index as a function-based index on the result.
The following example, like the previous one, creates a function-based index on the
major attribute of part-time students, but in this case, the hidden column for
major is associated with a substitutable object table
CREATE INDEX major3_idx ON person_obj_table p (TREAT(VALUE(p) AS part_time_student_typ).major);
Type evolution is the process of changing a object type. You can make the following changes to an object type:
Add and drop attributes
Add and drop methods
Modify a numeric attribute to increase its length, precision, or scale
Modify a varying length character attribute to increase its length
INSTANTIABLE properties of a type
Modify limit and size of
Modify length, precision, and scale of collection elements
Changes to a type affect things that reference the type. For example, if you add a new attribute to a type, data in a column of that type must be presented so as to include the new attribute.
This section includes the following topics:
Dependent schema objects of a type are objects that directly or indirectly reference the type and are affected by a change to it.
A type can have these kinds of dependent schema objects: tables; types or subtypes; program units (PL/SQL blocks) such as procedures, functions, packages, and triggers; indextypes; views (including object views); function-based indexes; and operators.
How a dependent schema object is affected by a change to a type depends on the object and on the nature of the change.
Dependent program units, views, operators, and indextypes are marked invalid when the type is modified. The next time one of these invalid schema objects is referenced, it is revalidated using the new type definition. If the object recompiles successfully, it becomes valid and can be used again.
Dependent function-based indexes may be dropped or disabled, depending on the type change, and must be rebuilt.
Dependent tables have one or more internal columns added for each attribute added to the type, depending on the attribute type. New attributes are added with
NULL values. For each dropped attribute, the columns associated with that attribute are dropped. For each modified attribute, the length, precision, or scale of its associated column is changed accordingly.
These changes mainly involve updating the metadata of the tables and can be performed quickly. However, the data in those tables must be updated to the format of the new type version as well, as discussed in "Options for Updating Data".
Depending on the amount of data, updating can be time-consuming, so the
ALTER TYPE command has options to let you choose whether to convert all dependent table data immediately or to leave it in the old format to be converted piecemeal as it is updated in the course of business.
CASCADE option for
ALTER TYPE propagates a type change to dependent types and tables. See "ALTER TYPE Statement for Type Evolution".
CASCADE itself has the following options that let you choose whether or not to convert table data to the new type format as part of the propagation:
INCLUDING TABLE DATA: converts the data (default)
NOT INCLUDING TABLE DATA : does not convert data
By default, the
CASCADE option converts the data. In either case, table data is always returned in the format of the latest type version. If the table data is stored in the format of an earlier type version, the database converts the data to the format of the latest version before returning it, even though the format in which the data is actually stored is not changed until the data is rewritten.
You can retrieve the definition of the latest type from the system view
USER_SOURCE. You can view definitions of all versions of a type in the
Oracle Database PL/SQL Language Reference for details about type specification and body compilation
Structural changes to a type affect dependent data and require the data to be converted. This is not true for changes that are confined to method definitions or behavior (implementation) of the type.
These possible changes to a type are structural:
Add or drop an attribute
Modify the length, precision, or scale of an attribute
Change the finality of a type from
NOT FINAL or the reverse
These changes result in new versions of the altered type and all its dependent types and require the system to add, drop, or modify internal columns of dependent tables as part of the process of converting to the new version.
When you make any of these kinds of changes to a type that has dependent types or tables, the effects of propagating the change are not confined only to metadata but also affect data storage arrangements and require data conversion.
Besides converting data, you may also need to make other changes. For example, if a new attribute is added to a type, and the type body invokes the constructor of the type, then each constructor in the type body must be modified to specify a value for the new attribute. Similarly, if a new method is added, then the type body must be replaced to add the implementation of the new method. The type body can be modified by using the
CREATE OR REPLACE TYPE BODY statement.
Example 8-5 illustrates how to make a simple change to
person_typ by adding one attribute and dropping another. The
CASCADE keyword propagates the type change to dependent types and tables, but the phrase
DATA prevents conversion of the related data.
-- Drop person_typ and person_obj_table if they exist CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20)); / CREATE TABLE person_obj_table OF person_typ; INSERT INTO person_obj_table VALUES (person_typ(12, 'Bob Jones', '650-555-0130')); SELECT value(p) FROM person_obj_table p;
VALUE(P)(IDNO, NAME, PHONE)
PERSON_TYP(12, 'Bob Jones', '650-555-0130')
You can add the email attribute and drop the phone attribute as follows:
ALTER TYPE person_typ ADD ATTRIBUTE (email VARCHAR2(80)), DROP ATTRIBUTE phone CASCADE NOT INCLUDING TABLE DATA;
Then disconnect and reconnect to accommodate the type change:
connect oe/oe; connect hr/hr; ALTER SESSION SET PLSQL_WARNINGS = 'enable:all'; -- The data of table person_obj_table has not been converted yet, but -- when the data is retrieved, Oracle returns the data based on -- the latest type version. The new attribute is initialized to NULL. SELECT value(p) FROM person_obj_table p;
VALUE(P)(IDNO, NAME, EMAIL)
PERSON_TYP(12, 'Bob Jones', NULL)
SELECT statements, even though column data may be converted to the latest type version, the converted data is not written back to the column. If you retrieve a particular user-defined type column in a table often, consider converting that data to the latest type version to eliminate redundant data conversions. Converting is especially beneficial if the column contains
VARRAY attributes which typically take more time to convert than objects or nested table columns.
You can convert a column of data by issuing an
UPDATE statement to set the column to itself, as indicated in the following code snippet, which is unrelated to previous code.
UPDATE dept_tab SET emp_array_col = emp_array_col;
You can convert all columns in a table by using
TABLE with the
DATA. For example:
ALTER TYPE person_typ ADD ATTRIBUTE (photo BLOB) CASCADE NOT INCLUDING TABLE DATA; ALTER TABLE person_obj_table UPGRADE INCLUDING DATA;
TABLE line converts only the table listed. The
CASCADE option prevents conversion of other tables or dependents.
This section describes the steps required to make a complex change to a type: the addition of a nested table attribute to an object type that is included in a nested table.
-- Drop existing person_typ, department_type, people_typ objects or tables CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20)); / -- creating a nested table type CREATE TYPE people_typ AS TABLE OF person_typ;/ CREATE TYPE department_typ AS OBJECT ( manager person_typ, employee people_typ); // a nested table/ CREATE TABLE department OF department_typ NESTED TABLE employee STORE AS employee_store_nt;
Example 8-7 starts by creating a new object
tasks_typ and a nested table type to hold it,
The following steps, both in Example 8-7, and in other programs, are necessary to add the nested table
tasks as an attribute to the object type
person_typ, which is already included in the nested table
ALTER TYPE..INVALIDATE statement to alter the type
person_typ. This statement bypasses all type and table checks to save time and invalidates dependent objects. You cannot access table data until it is validated.
TYPE statement includes
ATTRIBUTE to add the nested table
UPGRADE.. STORE AS clause upgrades the affected nested table, and specifies name of the new storage table.
-- Requires Ex. 8-6 CREATE TYPE tasks_typ AS OBJECT ( priority VARCHAR2(2), description VARCHAR2(30)); / CREATE TYPE tasks_nttab AS TABLE OF tasks_typ; / ALTER TYPE person_typ ADD ATTRIBUTE tasks tasks_nttab INVALIDATE; -- Propagate the change to employee_store_nt -- Specify a storage name for the new nested table ALTER TABLE employee_store_nt UPGRADE NESTED TABLE tasks STORE AS tasks_nt;
CREATE OR REPLACE TYPE BODY for
person_typ to update the corresponding type body to make it current with the new type definition, if necessary.
Upgrade the dependent tables to the latest type version and convert the data in the tables. This validates the table and allow for data access again.
ALTER TABLE department UPGRADE INCLUDING DATA;
Alter dependent PL/SQL program units as needed to take account of changes to the type.
Use OTT or JPublisher to generate new header files for applications, depending on whether the application is written in C or Java.
Adding a new attribute to a supertype also increases the number of attributes in all its subtypes because these inherit the new attribute. Inherited attributes always precede declared (locally defined) attributes, so adding a new attribute to a supertype causes the ordinal position of all declared attributes of any subtype to be incremented by one recursively. The mappings of the altered type must be updated to include the new attributes. Oracle Type Translator (OTT) and JPublisher do this. If you use another tool, you must be sure that the type headers are properly synchronized with the type definition in the server; otherwise, unpredictable behavior may result.
Modify application code as needed and rebuild the application.
When the system executes an
ALTER TYPE statement, it first validates the requested type change syntactically and semantically to make sure it is legal. The system performs the same validations as for a
CREATE TYPE statement plus some additional ones. If the new specification of the target type or any of its dependent types fails the type validations, the
ALTER TYPE statement aborts. No new type version is created, and all dependent objects remain unchanged.
If dependent tables exist, further checking ensures that restrictions relating to the tables and indexes are observed. For example, it ensures that an attribute being dropped is not used as a partitioning key. Again, if the
ALTER TYPE statement fails the check of table-related restrictions, then the type change is aborted, and no new version of the type is created.
When a single
ALTER TYPE statement adds multiple attributes, it is done in the order specified. Multiple type changes can be specified in the same
ALTER TYPE statement, but no attribute name or method signature can be specified more than once in the statement. For example, adding and modifying the same attribute in a single statement is not allowed.
The following sections contain other notes on type changes including:
Dropping all attributes from a root type is not allowed. Instead, you must drop the type. Because a subtype inherits all the attributes from its supertype, dropping all the attributes from a subtype does not reduce its attribute count to zero; therefore, dropping all attributes declared locally in a subtype is allowed.
Only an attribute declared locally in the target type can be dropped. You cannot drop an inherited attribute from a subtype. Instead, drop the attribute from the type where it is locally declared.
Dropping an attribute which is part of a table partitioning or sub-partitioning key in a table is not allowed.
Dropping an attribute of a primary key OID of an object table or an index-organized table (IOT) is not allowed.
When an attribute is dropped, the column corresponding to the dropped attribute is dropped.
When an attribute is dropped, any indexes, statistics, constraints, and referential integrity constraints that reference it are removed.
You are not allowed to expand the length of an attribute referenced in a function-based index, clustered key or domain index on a dependent table.
You are not allowed to decrease the length, precision, or scale of an attribute.
You can only drop a method from the type in which the method is defined (or redefined): You cannot drop an inherited method from a subtype, and you cannot drop an redefined method from a supertype.
If a method is not redefined, dropping it using the
CASCADE option removes the method from the target type and all subtypes. However, if a method is redefined in a subtype, the
CASCADE will fail and roll back. For the
CASCADE to succeed, you must first drop each redefined method from the subtype that defines it and then drop the method from the supertype.
You can consult the
USER_DEPENDENCIES table to find all the schema objects, including types, that depend on a given type. You can also run the
DBMS_UTILITY.GET_DEPENDENCY utility to find the dependencies of a type.
You can use the
INVALIDATE option to drop a method that has been redefined, but the redefined versions in the subtypes must still be dropped manually. The subtypes will remain in an invalid state until they are explicitly altered to drop the redefined versions. Until then, an attempt to recompile the subtypes for revalidation will produce the error
Method does not override.
INVALIDATE bypasses all the type and table checks and simply invalidates all schema objects dependent on the type. The objects are revalidated the next time they are accessed. This option is faster than using
CASCADE, but you must be certain that no problems occur when revalidating dependent types and tables. Table data cannot be accessed while a table is invalid; if a table cannot be validated, its data remains inaccessible.
Altering an object type from
NOT INSTANTIABLE is allowed only if the type has no table dependents.
Altering an object type from
NOT INSTANTIABLE to
INSTANTIABLE is allowed anytime. This change does not affect tables.
Altering an object type from
NOT FINAL to
FINAL is only allowed if the target type has no subtypes.
When you alter an object type from
NOT FINAL or vice versa, you must use
CASCADE to convert data in dependent columns and tables immediately. You may not use the
NOT INCLUDING TABLE DATA to defer converting data.
NOT FINAL to
FINAL, you must use
CASCADE INCLUDING TABLE DATA.
NOT FINAL, you may use either
CASCADE INCLUDING TABLE DATA or
CASCADE CONVERT TO SUBSTITUTABLE.
When you alter a type from
FINAL, select the
CASCADE option based on whether or not you want to insert new subtypes of the altered types into existing columns and tables.
By default, altering a type from
FINAL enables you to create new substitutable tables and columns of that type, but it does not automatically make existing columns (or object tables) of that type substitutable. In fact, just the opposite happens: existing columns and tables of the type are marked
LEVELS. If any embedded attribute of these columns is substitutable, an error is generated. New subtypes of the altered type cannot be inserted into these preexisting columns and tables.
To alter an object type to
FINAL in a way that makes existing columns and tables of the type substitutable (assuming that they are not marked
SUBSTITUTABLE), use the
CONVERT TO SUBSTITUTABLE.
Example 8-8 shows the use of
CASCADE with the option
CONVERT TO SUBSTITUTABLE:
CREATE TYPE shape AS OBJECT ( name VARCHAR2(30), area NUMBER) FINAL;/ ALTER TYPE shape NOT FINAL CASCADE CONVERT TO SUBSTITUTABLE;
CASCADE option marks each existing column as
SUBSTITUTABLE AT ALL LEVELS and causes a new, hidden column to be added for the TypeId of instances stored in the column. The column can then store subtype instances of the altered type.
INVALIDATE option of the
TYPE statement lets you alter a type without propagating the type change to dependent objects. In this case, the system does not validate the dependent types and tables, that is, does not ensure that all the ramifications of the type change are legal. Instead, the system marks all dependent schema objects invalid. These objects, including types and tables, are revalidated the next time they are referenced. If a type cannot be revalidated, it remains invalid, and any tables referencing it become inaccessible until the problem is corrected.
A table may fail validation for reasons such as: the addition of a new attribute to a type increased the number of columns in the table beyond the maximum of 1000, or an attribute used as a partitioning or clustering key of a table was dropped from a type.
To force a revalidation of a type, users can issue the
ALTER TYPE COMPILE statement. To force a revalidation of an invalid table, users can issue the
ALTER TABLE UPGRADE statement and specify whether or not the data is to be converted to the latest type version.
Note:In a system-triggered table validation, the table is referenced, table data is always updated to the latest type version: you do not have the option to postpone conversion of the data.
If a table cannot be converted to the latest type version, then
DELETE statements on the table are not allowed, and the table data becomes inaccessible. The following DDLs can be executed on the table, but all other statements which reference an invalid table are not allowed until the table is successfully validated:
All PL/SQL programs containing variables defined using
%ROWTYPE of a table or
%TYPE of a column or attribute from a table are compiled based on the latest type version. If the table fails the revalidation, then compiling any program units that reference that table also fails.
Table 8-1 lists some of the important options in the
...CASCADE statements for altering the attribute or method definition of a type.
Invalidates all dependent objects. Use this option to bypass all the type and table checks, and save time.
Use this option only if you are certain that problems will not be encountered revalidating dependent types and tables. Table data cannot be accessed again until it is validated; if it cannot be validated, it remains inaccessible.
Propagates the type change to dependent types and tables. The statement aborts if an error is found in dependent types or tables unless the
Converts data stored in all user-defined columns to the most recent version of the column type.
For each new attribute added to the column type, a new attribute is added to the data and is initialized to
Leaves column data as is, does not change type version. If an attribute is dropped from a type referenced by a table, the corresponding column of the dropped attribute is not removed from the table. However, the metadata of the column is marked unused. If the dropped attribute is stored out-of-line (for example,
This option is useful when you have many large tables and may run out of rollback segments if you convert them all in one transaction. This option enables you to convert the data of each dependent table later in a separate transaction (using an
Specifying this option speeds up the table upgrade because the table data remains in the format of the old type version. However, selecting data from this table requires converting the images stored in the column to the latest type version. This is likely to affect performance during subsequent
Because this option only requires updating the table metadata, it does not require that all tablespaces be on-line in read/write mode for the statement to succeed.
Forces the system to ignore errors from dependent tables and indexes. Errors are logged in a specified exception table so that they can be queried afterward. Use this option with caution because dependent tables can become inaccessible if some table errors occur.
For use when altering a type from
If the type is altered to
See Also:Oracle Database SQL Language Reference for further information about
You can use
TABLE to convert table data to the latest version of referenced types. For an example, see "Altering a Type by Adding a Nested Table Attribute". See Table 8-1 for a discussion of the
See Also:Oracle Database SQL Language Reference for information about
Beginning with Oracle Database 12c, you can use
TABLE to store ADTs with
XMLTYPEs and LOB attributes in
ANYDATA columns. You can also do the same for standalone XMLTYPEs.
To do this, use the
modify_opaque_type clause, which instructs the database to store these types unpacked. Otherwise, they cannot be stored in an
See Also:Oracle Database SQL Language Reference for information about
TABLEused with the
This section discusses various aspects of using system-defined constructors, also known as attribute-value constructors, and user-defined constructors.
This section includes these topics:
The system-defined constructor, also known as the attribute-value constructor, requires you to pass the constructor a value for each attribute of the type. The constructor then sets the attributes of the new object instance to those values, as shown in Example 8-9.
CREATE TYPE shape AS OBJECT ( name VARCHAR2(30), area NUMBER); / CREATE TABLE building_blocks of shape; -- attribute-value constructor: Sets instance attributes to the specified values INSERT INTO building_blocks VALUES ( NEW shape('my_shape', 4));
NEW preceding a call to a constructor is optional but recommended.
The attribute-value constructor saves you the trouble of defining your own constructors for a type. However, you must supply a value for every attribute declared in the type or the constructor call fails to compile.
This requirement can create a problem if you evolve the type later on, especially because the attribute-value constructor is implicit and not visible in the code, unlike a user-defined constructor. When you change the attributes of a type, the attribute-value constructor of the type changes, too. If you add an attribute, the updated attribute-value constructor expects a value for the new attribute; otherwise, any attribute-value constructor calls in your existing code fail.
See "Type Evolution".
User-defined constructors do not need to explicitly set a value for every attribute of a type, unlike attribute-value constructors. A user-defined constructor can have any number of arguments, of any type, and these do not need to map directly to type attributes. When you define a constructor, you can initialize the attributes to any appropriate values. For any attributes which you do not supply values, the system initialized to
If you evolve a type—for example, by adding an attribute—calls to user-defined constructors for the type do not need to be changed. User-defined constructors are not automatically modified when the type evolves, so their signatures remain the same. You may, however, need to change the definition of the constructor if you do not want the new attribute to be initialized to
You define user-defined constructors in the type body, like an ordinary method. You introduce the declaration and the definition with the phrase
CONSTRUCTOR FUNCTION and end with the clause
RETURN SELF AS RESULT.
A constructor for a type must have the same name as the type. Example 8-10 defines two constructor functions for the
shape type. As the example shows, you can overload user-defined constructors by defining multiple versions with different signatures.
CREATE TYPE shape AS OBJECT ( name VARCHAR2(30), area NUMBER, CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2) RETURN SELF AS RESULT, CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2, area NUMBER) RETURN SELF AS RESULT ) NOT FINAL; / CREATE TYPE BODY shape AS CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.name := name; SELF.area := 0; RETURN; END; CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2, area NUMBER) RETURN SELF AS RESULT IS BEGIN SELF.name := name; SELF.area := area; RETURN; END; END; /
A user-defined constructor has an implicit first parameter
SELF. Specifying this parameter in the declaration of a user-defined constructor is optional. If you do specify it, you must declare its mode to be
The required clause
RETURN SELF AS RESULT ensures that the most specific type of the instance being returned is the same as the most specific type of the
SELF argument. In the case of constructors, this is the type for which the constructor is defined. For example, if the most specific type of the
SELF argument on a call to the
shape constructor is
shape, then this clause ensures that the
shape constructor returns an instance of
shape (not an instance of a subtype of
When a constructor function is called, the system initializes the attributes of the
SELF argument to
NULL. Names of attributes subsequently initialized in the function body may be qualified with
SELF, such as
SELF.name in Example 8-10, to distinguish them from the names of the arguments of the constructor function, if these are the same. If the argument names are different, this qualification is not necessary.
The function body must include an explicit
return; as shown. The return keyword must not be followed by a
return expression. The system automatically returns the newly constructed
A user-defined constructor may be implemented in PL/SQL, C, or Java.
You can overload user-defined constructors, like other type methods.
User-defined constructors are not inherited, so a user-defined constructor defined in a supertype cannot be hidden in a subtype. However, a user-defined constructor does hide, and thus supersede, the attribute-value constructor for its type if the signature of the user-defined constructor exactly matches the signature of the attribute-value constructor. For the signatures to match, the names and types of the parameters (after the implicit
SELF parameter) of the user-defined constructor must be the same as the names and types of the attributes of the type. The mode of the parameters (after the implicit
SELF parameter) of the user-defined constructor must be
If an attribute-value constructor is not hidden by a user-defined constructor that has the same name and signature, the attribute-value constructor can still be called.
Note that, if you evolve a type—for example, by adding an attribute—the signature of the attribute-value constructor of the type changes accordingly. This can cause a formerly hidden attribute-value constructor to become usable again.
You call a user-defined constructor like any other function and you can use it anywhere you can use an ordinary function.
SELF argument is passed in implicitly and may not be passed in explicitly. In other words, usages like the following are not allowed:
NEW constructor(instance, argument_list)
A user-defined constructor cannot occur in the
DEFAULT clause of a
TABLE statement, but an attribute-value constructor can. The arguments to the attribute-value constructor must not contain references to PL/SQL functions or to other columns, including the pseudocolumns
ROWNUM, or to date constants that are not fully specified. The same is true for check constraint expressions: an attribute-value constructor can be used as part of check constraint expressions while creating or altering a table, but a user-defined constructor cannot.
Parentheses are required in SQL even for constructor calls that have no arguments. In PL/SQL, parentheses are optional when invoking a zero-argument constructor. They do, however, make it more obvious that the constructor call is a function call. The following PL/SQL example omits parentheses in the constructor call to create a new shape:
shape s := NEW my_schema.shape;
NEW keyword and the schema name are optional.
-- Requires Ex. 8-10 CREATE TYPE rectangle UNDER shape ( len NUMBER, wth NUMBER, CONSTRUCTOR FUNCTION rectangle(SELF IN OUT NOCOPY rectangle, name VARCHAR2, len NUMBER, wth NUMBER) RETURN SELF as RESULT, CONSTRUCTOR FUNCTION rectangle(SELF IN OUT NOCOPY rectangle, name VARCHAR2, side NUMBER) RETURN SELF as RESULT); / SHOW ERRORS CREATE TYPE BODY rectangle IS CONSTRUCTOR FUNCTION rectangle(SELF IN OUT NOCOPY rectangle, name VARCHAR2, len NUMBER, wth NUMBER) RETURN SELF AS RESULT IS BEGIN SELF.name := name; SELF.area := len*wth; SELF.len := len; SELF.wth := wth; RETURN ; END; CONSTRUCTOR FUNCTION rectangle(SELF IN OUT NOCOPY rectangle, name VARCHAR2, side NUMBER) RETURN SELF AS RESULT IS BEGIN SELF.name := name; SELF.area := side * side; SELF.len := side; SELF.wth := side; RETURN ; END; END; / CREATE TABLE shape_table OF shape; INSERT INTO shape_table VALUES(shape('shape1')); INSERT INTO shape_table VALUES(shape('shape2', 20)); INSERT INTO shape_table VALUES(rectangle('rectangle', 2, 5)); INSERT INTO shape_table VALUES(rectangle('quadrangle', 12, 3)); INSERT INTO shape_table VALUES(rectangle('square', 12));
The following query selects the rows in the
SELECT VALUE(s) FROM shape_table s;
RECTANGLE('rectangle', 10, 2, 5)
RECTANGLE('quadrangle', 36, 12, 3)
RECTANGLE('square', 144, 12, 12)
The following PL/SQL code calls the constructor:
s shape := NEW shape('void');
A SQLJ object type is a SQL object type mapped to a Java class. A SQLJ object type has an attribute-value constructor. It can also have user-defined constructors that are mapped to constructors in the referenced Java class.
CREATE TYPE address AS OBJECT EXTERNAL NAME 'university.address' LANGUAGE JAVA USING SQLData( street VARCHAR2(100) EXTERNAL NAME 'street', city VARCHAR2(50) EXTERNAL NAME 'city', state VARCHAR2(50) EXTERNAL NAME 'state', zipcode NUMBER EXTERNAL NAME 'zipcode', CONSTRUCTOR FUNCTION address (SELF IN OUT NOCOPY address, street VARCHAR2, city VARCHAR2, state VARCHAR2, zipcode NUMBER) RETURN SELF AS RESULT AS LANGUAGE JAVA NAME 'university.address (java.lang.String, java.lang.String, java.lang.String, int) return address'); /
A SQLJ type of a serialized representation can have only a user-defined constructor. The internal representation of an object of SQLJ type is opaque to SQL, so an attribute-value constructor is not possible for a SQLJ type.
Oracle database has three generic (that is, generically programmed) SQL data types that enable you to dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type, including object and collection types. You can also use these three types to create anonymous types, including anonymous collection types.
The three SQL types are implemented as opaque types. In other words, the internal structure of these types is not known to the database; their data can be queried only by implementing functions (typically 3GL routines) for the purpose. Oracle database provides both an OCI and a PL/SQL API for implementing such functions.
Of the three types,
ANYTYPE is transient, and
ANYDATASET are not transient, but rather persistent. Transient types cannot be persistently stored because their structures are opaque to the database. You cannot create columns of transient types or make them attributes of persistent types.
The three generic SQL types are described in Table 8-2.
A type description type. A
A self-describing data instance type. A
The following cannot be stored in an
A self-describing data set type. A
The following cannot be stored in an
Each of these three types can be used with any built-in type native to the database as well as with object types and collection types, both named and unnamed. The types provide a generic way to work dynamically with type descriptions, lone instances, and sets of instances of other types. Using the APIs, you can create a transient
ANYTYPE description of any kind of type. Similarly, you can create or convert (cast) a data value of any SQL type to an
ANYDATA and can convert an
ANYDATA (back) to a SQL type. And similarly again with sets of values and
The generic types simplify working with stored procedures. You can use the generic types to encapsulate descriptions and data of standard types and pass the encapsulated information into parameters of the generic types. In the body of the procedure, you can detail how to handle the encapsulated data and type descriptions of whatever type.
You can also store encapsulated data of a variety of underlying types in one table column of type
ANYDATASET. For example, you can use
ANYDATA with Advanced Queuing to model queues of heterogeneous types of data. You can query the data of the underlying data types like any other data.
Example 8-13 defines and executes a PL/SQL procedure that uses methods built into
SYS.ANYDATA to access information about data stored in a
SYS.ANYDATA table column.
CREATE OR REPLACE TYPE dogowner AS OBJECT ( ownerno NUMBER, ownername VARCHAR2(10) ); / CREATE OR REPLACE TYPE dog AS OBJECT ( breed VARCHAR2(10), dogname VARCHAR2(10) ); / CREATE TABLE mytab ( id NUMBER, data SYS.ANYDATA ); INSERT INTO mytab VALUES ( 1, SYS.ANYDATA.ConvertNumber (5) ); INSERT INTO mytab VALUES ( 2, SYS.ANYDATA.ConvertObject ( dogowner ( 5555, 'John') ) ); commit; CREATE OR REPLACE procedure P IS CURSOR cur IS SELECT id, data FROM mytab; v_id mytab.id%TYPE; v_data mytab.data%TYPE; v_type SYS.ANYTYPE; v_typecode PLS_INTEGER; v_typename VARCHAR2(60); v_dummy PLS_INTEGER; v_n NUMBER; v_dogowner dogowner; non_null_anytype_for_NUMBER exception; unknown_typename exception; BEGIN OPEN cur; LOOP FETCH cur INTO v_id, v_data; EXIT WHEN cur%NOTFOUND; v_typecode := v_data.GetType ( v_type /* OUT */ ); CASE v_typecode WHEN Dbms_Types.Typecode_NUMBER THEN IF v_type IS NOT NULL THEN RAISE non_null_anytype_for_NUMBER; END IF; v_dummy := v_data.GetNUMBER ( v_n /* OUT */ ); Dbms_Output.Put_Line ( To_Char(v_id) || ': NUMBER = ' || To_Char(v_n) ); WHEN Dbms_Types.Typecode_Object THEN v_typename := v_data.GetTypeName(); IF v_typename NOT IN ( 'HR.DOGOWNER' ) THEN RAISE unknown_typename; END IF; v_dummy := v_data.GetObject ( v_dogowner /* OUT */ ); Dbms_Output.Put_Line ( To_Char(v_id) || ': user-defined type = ' || v_typename || '(' || v_dogowner.ownerno || ', ' || v_dogowner.ownername || ' )' ); END CASE; END LOOP; CLOSE cur; EXCEPTION WHEN non_null_anytype_for_NUMBER THEN RAISE_Application_Error ( -20000, 'Paradox: the return AnyType instance FROM GetType ' || 'should be NULL for all but user-defined types' ); WHEN unknown_typename THEN RAISE_Application_Error ( -20000, 'Unknown user-defined type ' || v_typename || ' - program written to handle only HR.DOGOWNER' ); END; / SELECT t.data.gettypename() FROM mytab t; SET SERVEROUTPUT ON; EXEC P;
The query and the procedure P in the preceding code sample produce output like the following:
1: NUMBER = 5
2: user-defined type = HR.DOGOWNER(5555, John )
Corresponding to the three generic SQL types are three OCI types that model them. Each has a set of functions for creating and accessing the respective type:
OCIType: corresponds to
OCIAnyData: corresponds to
OCIAnyDataSet: corresponds to
Oracle Call Interface Programmer's Guide for the
OCIAnyDataSet APIs and details on how to use them
Oracle Database PL/SQL Packages and Types Reference for information about the interfaces to the
ANYDATASET types and the
DBMS_TYPES package, which defines constants for built-in and user-defined types, for use with
Oracle database provides a number of pre-defined aggregate functions such as
SUM for performing operations on a set of records. These pre-defined aggregate functions can be used only with scalar data. However, you can create your own custom implementations of these functions, or define entirely new aggregate functions, to use with complex data—for example, with multimedia data stored using object types, opaque types, and LOBs.
User-defined aggregate functions are used in SQL DML statements just like the Oracle database built-in aggregates. Once such functions are registered with the server, the database simply invokes the aggregation routines that you supplied instead of the native ones.
User-defined aggregates can be used with scalar data as well. For example, it may be worthwhile to implement special aggregate functions for working with complex statistical data associated with financial or scientific applications.
User-defined aggregates are a feature of the Extensibility Framework. You implement them using
ODCIAggregate interface routines.
See Also:Oracle Database Data Cartridge Developer's Guide for information on using the
ODCIAggregateinterface routines to implement user-defined aggregate functions
You can use nested table locators to improve performance when retrieving data.
Collection types do not map directly to a native type or structure in languages such as C++ and Java. An application using those languages must access the contents of a collection through Oracle database interfaces, such as OCI.
Generally, when the client accesses a nested table explicitly or implicitly (by fetching the containing object), the database returns the entire collection value to the client process. For performance reasons, a client may wish to delay or avoid retrieving the entire contents of the collection. Oracle database handles this case for you by using a locator instead of the actual nested table value. When you really access the contents of the collection, they are automatically transferred to the client.
A nested table locator is like a handle to the collection value. It attempts to preserve the value or copy semantics of the nested table by containing the database snapshot as of its time of retrieval. The snapshot helps the database retrieve the correct instantiation of the nested table value at a later time when the collection elements are fetched using the locator. The locator is scoped to a session and cannot be used across sessions. Because database snapshots are used, it is possible to get a
snapshot too old error if there is a high update rate on the nested table. Unlike a LOB locator, the nested table locator is truly a locator and cannot be used to modify the collection instance.
See Also:"Nested Table Locators" for more specific information