Online Documentation for SQL Manager for Oracle

Field parameters

The Fields tab of the Access Parameters dialog allows you to define a number of options pertaining to the fields of the external table.

 

Default field delimiter

Terminated by

Define the delimiter to show that everything between the current position in the record and the next occurrence of the termination string is considered part of the field, or select WHITESPACE from the drop-down list.

 

Start / End

If you set these parameters, the access driver starts at the current position in the record and skips over all whitespace looking for the first delimiter. All whitespace between the current position and the first delimiter is ignored. Next, the access driver looks for the second enclosure delimiter (or looks for the first one again if a second one is not specified). Everything between those two delimiters is considered part of the field.

 

Optionally

This option determines whether the Start / End delimiters can be either both present or both absent.

 

Field definitions

Trim

This option is used to specify that spaces should be trimmed from the beginning of a text field, the end of a text field, or both. Spaces include blanks and other nonprinting characters such as tabs, line feeds and carriage returns. Use the drop-down list to select one of the following values: DEFAULT, LDTRIM, LTRIM, RTRIM, LRTRIM, NOTRIM.

 

Missing field values are null

Indicates that if there is not enough data in a record for all fields, then those fields with missing data values are set to NULL.

 

Reject rows with all null fields

Indicates that a row will not be loaded into the external table if all referenced fields in the row are null.

 

 

The Field list area identifies the fields in the datafile and their data types.

Field Name

Identifies the name of a field in the datafile.

 

Data Type

Indicates the data type of the field. If no data type is selected, the access driver assumes the data type is CHAR(255).

 

Size

Defines the maximum size for the Data type.

 

StartPos

Number of bytes or characters from the beginning of the record to where the field begins. It positions the start of the field at an absolute spot in the record rather than relative to the position of the previous field.

 

EndPos

Indicates the absolute byte or character offset into the record for the last byte of the field.

 

Note that the Position parameter should be specified if you use StartPos / EndPos parameters.

 

Date format

Use this group to specify the date format and date mask (for date/time fields).

 

Default value

Use this group to specify the defaultlf and nulllf parameters.

 

Field delimiter

Use this group to specify the delimiter parameters for each field selected in the Field list, if necessary: Terminated by, Start / End, Trim, Max value, Optionally.

 

Default value

DefaultIf

Set the value to specify when the field is set to its default value.

 

NullIf

Set the value that specifies when the column associated with the field is set to NULL.

 

Field delimiter

Terminated by

Define the delimiter to show that everything between the current position in the record and the next occurrence of the termination string is considered part of the field, or select WHITESPACE from the drop-down list.

 

Start / End

If you set these parameters, the access driver starts at the current position in the record and skips over all whitespace looking for the first delimiter. All whitespace between the current position and the first delimiter is ignored. Next, the access driver looks for the second enclosure delimiter (or looks for the first one again if a second one is not specified). Everything between those two delimiters is considered part of the field.

 

Trim

Select the type of whitespace trimming for character fields.

 

Scale

Set the location of the decimal point in the number.

 

Optionally

This option determines whether the Start / End delimiters can be either both present or both absent.