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


   INFILE 'sample.dat'
   INTO TABLE employees_v3
1  TREAT AS hourly_emps_type
     (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:

  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.