||Oracle Tips by Burleson
Oracle 10g Unloading Columns Containing Final Object
The FINAL or NOT FINAL definition of an object
type determines whether subtypes can be derived from that type. Only
non-final types allow subtypes. A type is declared as not final by
including the NOT FINAL keyword in its type declaration. For
TYPE address_t AS OBJECT
This statement declares address_t to be a not
final type, such that subtypes of address_t can be defined. By
default, an object type is declared as final and subtypes cannot be
derived from it.
It is permitted to change a final type to a not
final type and vice versa with an ALTER TYPE statement. For example,
to change address_t to a final type:
Alteration of a type from NOT FINAL to FINAL is
only allowed if the target type has no subtypes.
Once created, a table can use types in place of
When final column objects are populated into an
external table, you move each sub-attribute in the object type into
its own column in the external table. In addition to columns for
each sub-attribute, the external table needs an additional column to
track whether the column object is atomically null. The following
demonstrates the unloading and loading of columns consisting of
final object types.
In the following example, the address column in
the external table is used to track whether the address column in
the source table is atomically NULL.
CREATE TABLE projects_obj_ext
Get the complete story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details: