pos_spec clause indicates the position of the column within the record. The setting of the
IN clause determines whether
pos_spec refers to byte positions or character positions. Using character positions with varying-width character sets takes significantly longer than using character positions with fixed-width character sets. Binary and multibyte character data should not be present in the same data file when
pos_spec is used for character positions. If they are, then the results are unpredictable. The syntax for the
pos_spec clause is as follows:
start parameter is the 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.
* parameter indicates that the field begins at the first byte or character after the end of the previous field. This is useful if you have a varying-length field followed by a fixed-length field. This option cannot be used for the first field in the record.
increment parameter positions the start of the field at a fixed number of bytes or characters from the end of the previous field. Use
increment to indicate that the start of the field starts before the current position in the record (this is a costly operation for multibyte character sets). Use
increment to move the start after the current position.
end parameter indicates the absolute byte or character offset into the record for the last byte of the field. If
start is specified along with
end cannot be less than
increment is specified along with
end, and the
start evaluates to an offset larger than the
end for a particular record, then that record will be rejected.
length parameter indicates that the end of the field is a fixed number of bytes or characters from the start. It is useful for fixed-length fields when the start is specified with
The following example shows various ways of using
pos_spec. It is followed by a sample of the data file that can be used to load it.
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth INT, phone CHAR(12), area_code CHAR(3), exchange CHAR(3), extension CHAR(4)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS PARAMETERS (FIELDS RTRIM (first_name (1:15) CHAR(15), last_name (*:+20), year_of_birth (36:39), phone (40:52), area_code (*-12: +3), exchange (*+1: +3), extension (*+1: +4))) LOCATION ('info.dat')); Alvin Tolliver 1976415-922-1982 Kenneth Baer 1963212-341-7912 Mary Dube 1973309-672-2341