Specifying Filler Fields

A filler field, specified by BOUNDFILLER or FILLER is a data file mapped field that does not correspond to a database column. Filler fields are assigned values from the data fields to which they are mapped.

Keep the following in mind regarding filler fields:

  • The syntax for a filler field is same as that for a column-based field, except that a filler field's name is followed by FILLER.

  • Filler fields have names but they are not loaded into the table.

  • Filler fields can be used as arguments to init_specs (for example, NULLIF and DEFAULTIF).

  • Filler fields can be used as arguments to directives (for example, SID, OID, REF, and BFILE).

    To avoid ambiguity, if a Filler field is referenced in a directive, such as BFILE, and that field is declared in the control file inside of a column object, then the field name must be qualified with the name of the column object. This is illustrated in the following example:

    LOAD DATA 
    INFILE * 
    INTO TABLE BFILE1O_TBL REPLACE 
    FIELDS TERMINATED BY ',' 
    ( 
       emp_number char, 
       emp_info_b column object 
       ( 
       bfile_name FILLER char(12), 
       emp_b BFILE(constant "SQLOP_DIR", emp_info_b.bfile_name) NULLIF 
      emp_info_b.bfile_name = 'NULL' 
       ) 
    ) 
    BEGINDATA 
    00001,bfile1.dat, 
    00002,bfile2.dat, 
    00003,bfile3.dat, 
    
  • Filler fields can be used in field condition specifications in NULLIF, DEFAULTIF, and WHEN clauses. However, they cannot be used in SQL strings.

  • Filler field specifications cannot contain a NULLIF or DEFAULTIF clause.

  • Filler fields are initialized to NULL if TRAILING NULLCOLS is specified and applicable. If another field references a nullified filler field, then an error is generated.

  • Filler fields can occur anyplace in the data file, including inside the field list for an object or inside the definition of a VARRAY.

  • SQL strings cannot be specified as part of a filler field specification, because no space is allocated for fillers in the bind array.

    Note:

    The information in this section also applies to specifying bound fillers by using BOUNDFILLER. The only exception is that with bound fillers, SQL strings can be specified as part of the field, because space is allocated for them in the bind array.

A sample filler field specification looks as follows:

 field_1_count FILLER char,
 field_1 varray count(field_1_count)
 (
    filler_field1  char(2),
    field_1  column object
    (
      attr1 char(2),
      filler_field2  char(2),
      attr2 char(2),
    )
    filler_field3  char(3),
 )
 filler_field4 char(6)