Loading Object Tables with a Subtype

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,

Note:

The callouts, in bold, to the left of the example correspond to the following notes:

  1. 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

  2. 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.