Restrictions in Nested Tables and VARRAYs

The following restrictions exist for nested tables and VARRAYs:

  • 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

Note:

The callouts, in bold, to the left of the example correspond to the following notes:

  1. The TERMINATED BY clause specifies the VARRAY instance terminator (note that no COUNT clause is used).

  2. Full name field references (using dot notation) resolve the field name conflict created by the presence of this filler field.

  3. proj_cnt is a filler field used as an argument to the COUNT clause.

  4. This entry specifies the following:

    • 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.