Distinguishing Different Input Row Object Subtypes

A single data file may contain records made up of row objects inherited from the same base row object type. For example, consider the following simple object type and object table definitions, in which a nonfinal base object type is defined along with two object subtypes that inherit their row objects from the base type:

CREATE TYPE person_t AS OBJECT 
 (name    VARCHAR2(30), 
  age     NUMBER(3)) not final; 

CREATE TYPE employee_t UNDER person_t 
 (empid   NUMBER(5), 
  deptno  NUMBER(4), 
  dept    VARCHAR2(30)) not final; 

CREATE TYPE student_t UNDER person_t 
 (stdid   NUMBER(5), 
  major   VARCHAR2(20)) not final; 

CREATE TABLE persons OF person_t;

The following input data file contains a mixture of these row objects subtypes. A type ID field distinguishes between the three subtypes. person_t objects have a P in the first column, employee_t objects have an E, and student_t objects have an S.

P,James,31, 
P,Thomas,22, 
E,Pat,38,93645,1122,Engineering, 
P,Bill,19, 
P,Scott,55, 
S,Judy,45,27316,English, 
S,Karen,34,80356,History, 
E,Karen,61,90056,1323,Manufacturing, 
S,Pat,29,98625,Spanish, 
S,Cody,22,99743,Math, 
P,Ted,43, 
E,Judy,44,87616,1544,Accounting, 
E,Bob,50,63421,1314,Shipping, 
S,Bob,32,67420,Psychology, 
E,Cody,33,25143,1002,Human Resources,

The following control file uses relative positioning based on the POSITION parameter to load this data. Note the use of the TREAT AS clause with a specific object type name. This informs SQL*Loader that all input row objects for the object table will conform to the definition of the named object type.

Note:

Multiple subtypes cannot be loaded with the same INTO TABLE statement. Instead, you must use multiple INTO TABLE statements and have each one load a different subtype.

INTO TABLE persons 
REPLACE 
WHEN typid = 'P' TREAT AS person_t 
FIELDS TERMINATED BY "," 
 (typid   FILLER  POSITION(1) CHAR, 
  name            CHAR, 
  age             CHAR) 

INTO TABLE persons 
REPLACE 
WHEN typid = 'E' TREAT AS employee_t 
FIELDS TERMINATED BY "," 
 (typid   FILLER  POSITION(1) CHAR, 
  name            CHAR, 
  age             CHAR, 
  empid           CHAR, 
  deptno          CHAR, 
  dept            CHAR) 

INTO TABLE persons 
REPLACE 
WHEN typid = 'S' TREAT AS student_t 
FIELDS TERMINATED BY "," 
 (typid   FILLER  POSITION(1) CHAR, 
  name            CHAR, 
  age             CHAR, 
  stdid           CHAR, 
  major           CHAR)