ORCL - CONTINUEIF

About 17 years ago, my 1st IT job is maintaining a sql load program for hospital medical records at Alberta Health. Today we have an imodule routine to fetch the records and load into database.

My colleague had some trouble with the data with embedded line breaks before ending the recordes, and ask me if I could help. I could not help to jump on my "first love" :)) special feeling about sqlloader:))

sqlloader offer two possibilities :  - courtesy to http://gerardnico.com/wiki/!

• if you have a special character at the end of each line: the stream record format

Using the STR attribute, we can specify a new end-of-line character (or sequence of characters). This allows us to create an input data file that has some special character at the end of each line. The newline is no longer special.

• if you don't have a special character at the end of each line: the CONTINUEIF statement.

Here is the sample data for us:

"100045678","address is
super street
18999 Edmonton Alberta
T6R4Y3" ............................................
"2","the degree is MBA
UT
1998" ............................................

 

The each field inside data looks enclosed with double quotes "xxx", so we could simple add the CONTINUEIF to control the end of records.

 

From Oracle:

Using CONTINUEIF to Assemble Logical Records

Use CONTINUEIF if the number of physical records to be combined varies. The CONTINUEIF clause is followed by a condition that is evaluated for each physical record, as it is read. For example, two records might be combined if a pound sign (#) were in byte 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:

Description of the illustration continueif.gif

Table 8-2 describes the parameters for the CONTINUEIF clause.

Therefore we ask the loader to continue the record if see a break line not followed by a double quote, which means it's not the end of records!

LOAD DATA
INFILE 'giftdata.dat'
CONTINUEIF LAST != '"'
INTO TABLE conversion
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
"ID",
"DESCRIPTION" CHAR(500)
)

Cheers!