Example 11-4 shows a case in which a nonfinal base object type has been extended to create a new derived subtype. Although the column object in the table definition is declared to be of the base object type, SQL*Loader allows any subtype to be loaded into the column object, provided that the subtype is derived from the base object type.
Example 11-4 Loading Column Objects with a Subtype
Object Type Definitions
CREATE TYPE person_type AS OBJECT (name VARCHAR(30), ssn NUMBER(9)) not final; CREATE TYPE employee_type UNDER person_type (empid NUMBER(5)); CREATE TABLE personnel (deptno NUMBER(3), deptname VARCHAR(30), person person_type);
Control File Contents
LOAD DATA INFILE 'sample.dat' INTO TABLE personnel FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (deptno INTEGER EXTERNAL(3), deptname CHAR, 1 person COLUMN OBJECT TREAT AS employee_type (name CHAR, ssn INTEGER EXTERNAL(9), 2 empid INTEGER EXTERNAL(5)))
Data File (sample.dat)
101,Mathematics,Johny Q.,301189453,10249, 237,Physics,"Albert Einstein",128606590,10030,
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 column object person
as if it were declared to be of the derived type employee_type
, instead of its actual declared type, person_type
.
The empid
attribute is allowed here because it is an attribute of the employee_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 column's declared type.