Re: Loader Question?

> However, The rep from Oracle got here before the
> documentation (which is on order) and I don't have
> the manuals yet.
>
> There is quite a bit of online help for everything except
> SQL*Loader.

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'

---
Name   : Lun Wing San (Certified Oracle Database Administrator)

Title  : Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.