The following restrictions exist for nested tables and VARRAY
s:
A field_list
cannot contain a collection_fld_spec
.
A col_obj_spec
nested within a VARRAY
cannot contain a collection_fld_spec
.
The column_name
specified as part of the field_list
must be the same as the column_name
preceding the VARRAY
parameter.
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:
The TERMINATED
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 COUNT
clause.
An SDF called pr
.txt
as the source of data. It also specifies a fixed-record format within the SDF.
If 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.