The following restrictions exist for nested tables and
field_list cannot contain a
col_obj_spec nested within a
VARRAY cannot contain a
column_name specified as part of the
field_list must be the same as the
column_name preceding the
Also, be aware that if you are loading into a table containing nested tables, then SQL*Loader will not automatically split the load into multiple loads and generate a set ID.
Example 11-24 demonstrates loading a
VARRAY and a nested table.
Example 11-24 Loading a VARRAY and a Nested Table
Control File Contents
LOAD DATA INFILE 'sample.dat' "str '\n' " INTO TABLE dept REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( dept_no CHAR(3), dname CHAR(25) NULLIF dname=BLANKS, 1 emps VARRAY TERMINATED BY ':' ( emps COLUMN OBJECT ( name CHAR(30), age INTEGER EXTERNAL(3), 2 emp_id CHAR(7) NULLIF emps.emps.emp_id=BLANKS ) ), 3 proj_cnt FILLER CHAR(3), 4 projects NESTED TABLE SDF (CONSTANT "pr.txt" "fix 57") COUNT (proj_cnt) ( projects COLUMN OBJECT ( project_id POSITION (1:5) INTEGER EXTERNAL(5), project_name POSITION (7:30) CHAR NULLIF projects.projects.project_name = BLANKS ) ) )
Data File (sample.dat)
101,MATH,"Napier",28,2828,"Euclid", 123,9999:0 210,"Topological Transforms",:2
Secondary Data File (SDF) (pr.txt)
21034 Topological Transforms 77777 Impossible Proof
The callouts, in bold, to the left of the example correspond to the following notes:
BY clause specifies the
VARRAY instance terminator (note that no
COUNT clause is used).
Full name field references (using dot notation) resolve the field name conflict created by the presence of this filler field.
proj_cnt is a filler field used as an argument to the
An SDF called
txt as the source of data. It also specifies a fixed-record format within the SDF.
COUNT is 0, then the collection is initialized to empty. Another way to initialize a collection to empty is to use a
DEFAULTIF clause. The main field name corresponding to the nested table field description is the same as the field name of its nested nonfiller-field, specifically, the name of the column object field description.