The Oracle database automatically supplies a default constructor for every object type. This constructor requires that all attributes of the type be specified as arguments in a call to the constructor. When a new instance of the object is created, its attributes take on the corresponding values in the argument list. This constructor is known as the attribute-value constructor. SQL*Loader uses the attribute-value constructor by default when loading column objects.
It is possible to override the attribute-value constructor by creating one or more user-defined constructors. When you create a user-defined constructor, you must supply a type body that performs the user-defined logic whenever a new instance of the object is created. A user-defined constructor may have the same argument list as the attribute-value constructor but differ in the logic that its type body implements.
When the argument list of a user-defined constructor function matches the argument list of the attribute-value constructor, there is a difference in behavior between conventional and direct path SQL*Loader. Conventional path mode results in a call to the user-defined constructor. Direct path mode results in a call to the attribute-value constructor. Example 11-7 illustrates this difference.
Example 11-7 Loading a Column Object with Constructors That Match
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),
-- User-defined constructor that looks like an attribute-value constructor
CONSTRUCTOR FUNCTION
employee_type (name VARCHAR2, ssn NUMBER, empid NUMBER)
RETURN SELF AS RESULT);
CREATE TYPE BODY employee_type AS
CONSTRUCTOR FUNCTION
employee_type (name VARCHAR2, ssn NUMBER, empid NUMBER)
RETURN SELF AS RESULT AS
--User-defined constructor makes sure that the name attribute is uppercase.
BEGIN
SELF.name := UPPER(name);
SELF.ssn := ssn;
SELF.empid := empid;
RETURN;
END;
CREATE TABLE personnel
(deptno NUMBER(3),
deptname VARCHAR(30),
employee employee_type);
Control File Contents
LOAD DATA
INFILE *
REPLACE
INTO TABLE personnel
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(deptno INTEGER EXTERNAL(3),
deptname CHAR,
employee COLUMN OBJECT
(name CHAR,
ssn INTEGER EXTERNAL(9),
empid INTEGER EXTERNAL(5)))
BEGINDATA
1 101,Mathematics,Johny Q.,301189453,10249,
237,Physics,"Albert Einstein",128606590,10030,
The callout, in bold, to the left of the example corresponds to the following note:
When this control file is run in conventional path mode, the name fields, Johny Q. and Albert Einstein, are both loaded in uppercase. This is because the user-defined constructor is called in this mode. In contrast, when this control file is run in direct path mode, the name fields are loaded exactly as they appear in the input data. This is because the attribute-value constructor is called in this mode.
It is possible to create a user-defined constructor whose argument list does not match that of the attribute-value constructor. In this case, both conventional and direct path modes will result in a call to the attribute-value constructor. Consider the definitions in Example 11-8.
Example 11-8 Loading a Column Object with Constructors That Do Not Match
Object Type Definitions
CREATE SEQUENCE employee_ids
START WITH 1000
INCREMENT BY 1;
CREATE TYPE person_type AS OBJECT
(name VARCHAR(30),
ssn NUMBER(9)) not final;
CREATE TYPE employee_type UNDER person_type
(empid NUMBER(5),
-- User-defined constructor that does not look like an attribute-value
-- constructor
CONSTRUCTOR FUNCTION
employee_type (name VARCHAR2, ssn NUMBER)
RETURN SELF AS RESULT);
CREATE TYPE BODY employee_type AS
CONSTRUCTOR FUNCTION
employee_type (name VARCHAR2, ssn NUMBER)
RETURN SELF AS RESULT AS
-- This user-defined constructor makes sure that the name attribute is in
-- lowercase and assigns the employee identifier based on a sequence.
nextid NUMBER;
stmt VARCHAR2(64);
BEGIN
stmt := 'SELECT employee_ids.nextval FROM DUAL';
EXECUTE IMMEDIATE stmt INTO nextid;
SELF.name := LOWER(name);
SELF.ssn := ssn;
SELF.empid := nextid;
RETURN;
END;
CREATE TABLE personnel
(deptno NUMBER(3),
deptname VARCHAR(30),
employee employee_type);
If the control file described in Example 11-7 is used with these definitions, then the name fields are loaded exactly as they appear in the input data (that is, in mixed case). This is because the attribute-value constructor is called in both conventional and direct path modes.
It is still possible to load this table using conventional path mode by explicitly making reference to the user-defined constructor in a SQL expression. Example 11-9 shows how this can be done.
Example 11-9 Using SQL to Load Column Objects When Constructors Do Not Match
Control File Contents
LOAD DATA
INFILE *
REPLACE
INTO TABLE personnel
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(deptno INTEGER EXTERNAL(3),
deptname CHAR,
name BOUNDFILLER CHAR,
ssn BOUNDFILLER INTEGER EXTERNAL(9),
1 employee EXPRESSION "employee_type(:NAME, :SSN)")
BEGINDATA
1 101,Mathematics,Johny Q.,301189453,
237,Physics,"Albert Einstein",128606590,
The callouts, in bold, to the left of the example correspond to the following note:
When this control file is run in conventional path mode, the name fields, Johny Q. and Albert Einstein, are both loaded in uppercase. This is because the user-defined constructor is called in this mode. In contrast, when this control file is run in direct path mode, the name fields are loaded exactly as they appear in the input data. This is because the attribute-value constructor is called in this mode.
If the control file in Example 11-9 is used in direct path mode, then the following error is reported:
SQL*Loader-951: Error calling once/load initialization ORA-26052: Unsupported type 121 for SQL expression on column EMPLOYEE.