Oracle7 Server Utilities

Assembling Logical Records from Physical Records

You can create one logical record from multiple physical records using one of the following two clauses, depending on your data:

CONCATENATE

CONTINUEIF

CONCATENATE is appropriate in the simplest case, when SQL*Loader should always add the same number of physical records to form one logical record. The syntax is:

CONCATENATE  n

where n indicates the number of physical records to combine.

If the number of physical records to be continued varies, then CONTINUEIF must be used. The keyword CONTINUEIF is followed by a condition that is evaluated for each physical record, as it is read. For example, two records might be combined if there were a pound sign (#) in character position 80 of the first record. If any other character were there, the second record would not be added to the first. The full syntax for CONTINUEIF adds even more flexibility:

where:

THIS If the condition is true in this record, then the next physical record is read and concatenated to the current physical record, continuing until the condition is false. If the condition is false in the current record, then the current physical record is the last physical record of the current logical record. THIS is the default.
NEXT If the condition is true in the next record, then the next physical record is concatenated to the current record, continuing until the condition is false.
If the condition is false in the next record, then the current physical record is the last physical record of the current logical record.
pos_spec Indicates starting and ending column numbers in the physical record, as shown below:

Column numbers start with 1. Either a hyphen or a colon is acceptable (start-end or start:end).
If you omit end, the length of the continuation field is the length of the byte string or character string. If you use end, and the length of the resulting continuation field is not the same as that of the byte string or character string, the shorter one is padded. Character strings are padded with blanks, hexadecimal strings with zeros.
LAST This test is similar to THIS, but the test is always against the last non-blank character. If the last non-blank character in this physical record meets the test, then the next physical record is read and concatenated to the current physical record, continuing until the condition is false. If the condition is false in the current record, then the current physical record is the last physical record of the current logical record.
operator The supported operators are equal and not equal:

				=    !=     ¬=   <>

For the equal operator, the field and comparison string must match exactly for the condition to be true. For the not equal operator, they may differ in any character.
char_string A string of characters to be compared to the continuation field defined by start and end, according to the operator. The string must be enclosed in double or single quotation marks. The comparison is made character by character, blank padding on the right if necessary.
X'hex_string' A string of bytes in hexadecimal format, used in the same way as the character string above. X'1FB033' would represent the three bytes with values 1F, B0 and 33 (hex).

Note: The positions in the CONTINUEIF clause refer to positions in each physical record. This is the only time you refer to character positions in physical records. All other references are to logical records.

For CONTINUEIF THIS and CONTINUEIF NEXT, the continuation field is removed from all physical records before the logical record is assembled. This allows data values to span the records with no extra characters (continuation characters) in the middle. Two examples showing CONTINUEIF THIS and CONTINUEIF NEXT follow:

CONTINUEIF THIS			CONTINUEIF NEXT

(1:2) = '%%'  			(1:2) ='%%'

Assume physical data records 12 characters long and that a period means a space:

%%aaaaaaaa....		..aaaaaaaa....

%%bbbbbbbb....		%%bbbbbbbb....

..cccccccc....		%%cccccccc.... 
%%dddddddddd..		..dddddddddd..

%%eeeeeeeeee..		%%eeeeeeeeee..

..ffffffffff..		%%ffffffffff..

The logical records would be the same in each case:

aaaaaaaa....bbbbbbbb....cccccccc....

dddddddddd..eeeeeeeeee..ffffffffff..

Notes:

CONTINUEIF LAST differs from CONTINUEIF THIS and CONTINUEIF NEXT. With CONTINUEIF LAST the continuation character is not removed from the physical record. Instead, this character is included when the logical record is assembled.

Trailing blanks in the physical records are part of the logical records.

Examples of How to Specify CONTINUEIF

In the first example, you specify that if the current physical record (record1) has an asterisk in column 1. Then the next physical record (record2) should be appended to it. If record2 also has an asterisk in column 1, then record3 is appended also.

If record2 does not have an asterisk in column 1, then it is still appended to record1, but record3 begins a new logical record.

CONTINUEIF THIS (1) = "*"

In the next example, you specify that if the current physical record (record1) has a comma in the last non-blank data column. Then the next physical record (record2) should be appended to it. If a record does not have a comma in the last column, it is the last physical record of the current logical record.

CONTINUEIF LAST = ","

In the last example, you specify that if the next physical record (record2) has a "10" in columns 7 and 8. Then it should be appended to the preceding physical record (record1). If a record does not have a "10" in columns 7 and 8, then it begins a new logical record.

CONTINUEIF NEXT (7:8) = '10'

Case 4 shows the CONTINUEIF clause in use.

THIS	If the condition is true in this record, then the next physical record is read and concatenated to the current physical record, continuing until the condition is false. If the condition is false in the current record, then the current physical record is the last physical record of the current logical record. THIS is the default.
NEXT	If the condition is true in the next record, then the next physical record is concatenated to the current record, continuing until the condition is false.
	If the condition is false in the next record, then the current physical record is the last physical record of the current logical record.
pos_spec	Indicates starting and ending column numbers in the physical record, as shown below:

	Column numbers start with 1. Either a hyphen or a colon is acceptable (start-end or start:end).
	If you omit end, the length of the continuation field is the length of the byte string or character string. If you use end, and the length of the resulting continuation field is not the same as that of the byte string or character string, the shorter one is padded. Character strings are padded with blanks, hexadecimal strings with zeros.
LAST	This test is similar to THIS, but the test is always against the last non-blank character. If the last non-blank character in this physical record meets the test, then the next physical record is read and concatenated to the current physical record, continuing until the condition is false. If the condition is false in the current record, then the current physical record is the last physical record of the current logical record.
operator	The supported operators are equal and not equal:

	For the equal operator, the field and comparison string must match exactly for the condition to be true. For the not equal operator, they may differ in any character.
char_string	A string of characters to be compared to the continuation field defined by start and end, according to the operator. The string must be enclosed in double or single quotation marks. The comparison is made character by character, blank padding on the right if necessary.
X'hex_string'	A string of bytes in hexadecimal format, used in the same way as the character string above. X'1FB033' would represent the three bytes with values 1F, B0 and 33 (hex).