Fixed-length format options

Certain format options are valid only for fixed-length format data loads, and some options have a different behavior when you use them for fixed-length format data loads.

Fixed-length-only options

The following external table options are valid only for the fixed-length format:
Table 1. Fixed-length-only options
Option Meaning
RecordLength Specifies the length of the entire record, including null-indicator bytes (if any) and excluding the record delimiter (if any). The value is an integer constant. There is no default value.
Layout Defines the location of the fields of the input record. Specify comma-separated zone definitions within braces { }. This option is mandatory for the fixed-length format; there is no default value.

Changes in option behavior for the fixed-length format

The following external table options have a different meaning for the fixed-length format:
Table 2. Changes in option behavior for fixed-length format
Option Meaning
CtrlChars
Text-delimited:
  • If the value is False (the default), unescaped control characters (except \t) cause an error, with one exception. The exception occurs if the value of the CtrlChars option is False and the value of the CRinString option is True or On; in that case, you can use \r (carriage return) without causing an error.
  • If the value is True, unescaped control characters \0 and \n cause an error, as does \r if the value of the CrInString option is False.
Fixed-length:
  • If the value is True, all unescaped characters are allowed.
  • If the value is False (the default), unescaped characters cause an error, except for \t and \n (and \r if the value of the CRinString option is True or On).
CrInString Text-delimited: Augments the CtrlChars option behaviors. See the CtrlChars option information.

Fixed-length: Used only when the value of the CtrlChars option is OFF.
MaxErrors Sets the maximum number of allowed (non-fatal) errors before the load is stopped. Because the parser reports errors for each field or zone rather than one error for the row, multiple errors can be reported for the same row, so you must set a limit. When the parser detects an error in a field or zone, it recovers by using the field or zone length and continues from the next field or zone, until the end of record is reached, an unrecoverable error occurs, or the MaxErrors limit is reached.
Unrecoverable errors include the following ones:
  • The record length of the file does not match the value of the RecordLength option.
  • The file does not use the delimiter that you specified for the RecordDelim option.
  • The RecordLength value is invalid (is negative value or zero).
  • The zone length is invalid (is a negative value).
  • The UTF-8 initial byte is invalid.
  • The UTF-8 continuation bytes are invalid.

Unsupported options

The following external table options are not supported for the fixed-length format. If you set them, an error results.
  • Delimiter
  • Encoding
  • EscapeChar
  • FillRecord
  • IgnoreZero
  • IncludeZeroSeconds
  • LFinString
  • QuotedValue
  • RequireQuotes
  • TimeExtraZeros
  • TruncString

Default zone values

The following external table options provide default values for zone definitions:
NullValue
Default for the ‘NULLIF’ clause of all zones.
DateStyle, DateDelim, TimeStyle, TimeDelim, BoolStyle
Default for zone style for corresponding date, time, and boolean zones.