If an object table's row object is based on a nonfinal type, then SQL*Loader allows for any derived subtype to be loaded into the object table. As previously mentioned, the syntax required to load an object table with a derived subtype is almost identical to that used for a typical relational table. However, in this case, the actual subtype to be used must be named, so that SQL*Loader can determine if it is a valid subtype for the object table. This concept is illustrated in Example 11-12.
Example 11-12 Loading an Object Table with a Subtype
Object Type Definitions
CREATE TYPE employees_type AS OBJECT (name VARCHAR2(30), age NUMBER(3), emp_id NUMBER(5)) not final; CREATE TYPE hourly_emps_type UNDER employees_type (hours NUMBER(3)); CREATE TABLE employees_v3 of employees_type;
Control File Contents
LOAD DATA INFILE 'sample.dat' INTO TABLE employees_v3 1 TREAT AS hourly_emps_type FIELDS TERMINATED BY ',' (name CHAR(30), age INTEGER EXTERNAL(3), emp_id INTEGER EXTERNAL(5), 2 hours INTEGER EXTERNAL(2))
Data File (sample.dat)
Johny Quest, 18, 007, 32, Speed Racer, 16, 000, 20,
The callouts, in bold, to the left of the example correspond to the following notes:
The TREAT
AS
clause indicates that SQL*Loader should treat the object table as if it were declared to be of type hourly_emps_type
, instead of its actual declared type, employee_type
The hours
attribute is allowed here because it is an attribute of the hourly_emps_type
. If the TREAT
AS
clause had not been specified, then this attribute would have resulted in an error, because it is not an attribute of the object table's declared type.