||Oracle Tips by Burleson
Oracle 10g Tables of Final Objects Types
One of the differences between "normal" and
"object" tables is that object tables have an object identifier that
uniquely identifies every row in the table (not a rowid).
With object tables, the following situations
There is no need to unload and reload the
object identifier; in this case, the external table only needs
to contain fields for the attributes of the type for the object
table (an object table is created from a type definition).
object identifier needs to be unloaded and reloaded and the
object identifier for the table has been defined as one or more
fields in the table, (also known as primary key-based object
identifiers). In this case, the external table has one column
for every attribute of the type for the table.
The object identifier needs to be unloaded and
the object identifier for the table is system-generated. In this
case the procedure becomes more complex. In addition to a column for
each of the attributes of the type, you need to add another column
to hold the system-generated OID.
The following example demonstrates the third
First we create an object table with
CREATE TYPE address_t AS OBJECT
2 ( street_number NUMBER,
3 Street_name VARCHAR2(30),
4 suite_no VARCHAR2(10),<
8 country VARCHAR2(32))
CREATE TABLE addresses OF address_t;
(By default object tables use system-generated
object identifiers, so there is no need to specify any special
INSERT INTO addresses VALUES ('1313','Mockingbird Lane','','Sweet
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: