SQL*Loader: Preserving CR/LF in large text fields?

From: Brian Fenske <brianf_at_purpleinc.com>
Date: 1997/06/18
Message-ID: <01bc7c0c$e7382740$52486dcf@oahu.purpleinc.com>#1/1

I have been given the task of using SQL*Loader to populate an Oracle database. The data I have looks like the following (this is the first record to be processed):

D0100S001~NOTE: This is a derivation SCN to derive

       an MD-11 aircraft from 5500-36M     
       per Memo C1-E73-JAF-77 dated 10     
       October 1985.                       

~

The Control file I have been given looks like:

LOAD DATA
INFILE 'K:\CDMS2GEN\scnwsaf.dat'
INSERT
INTO TABLE SCN_WSAFNote
FIELDS TERMINATED BY '~'
(RSKNUMBER,
 WSAF_NOTES char(20000) terminated by '~')

The error I get is:

Record 1: Rejected - Error on table SCN_WSAFNote, column WSAF_NOTES. Column not found before end of logical record (use TRAILING NULLCOLS)

What I want is for the field WSAF_NOTES to contain carriage-return/line-feed characters after each line. In other words, the data as it appears in this message is how I want it stored. Like this:

NOTE: This is a derivation SCN to derive

       an MD-11 aircraft from 5500-36M     
       per Memo C1-E73-JAF-77 dated 10     
       October 1985.                       

If I use the CONTINUEIF NEXT '~' construct won't I get the multiple lines of text all run together? I saw how one person gave an example of modifying the data as it loads like this:

 LOAD DATA
   INFILE *
   INTO TABLE modified_data

   (  rec_no                      "my_db_sequence.nextval",
      time_loaded                 "to_char(SYSDATE, 'HH24:MI')",
      data1        POSITION(1:5)  ":data2/100",
      data2        POSITION(6:15) "upper(:data1)"

   )
   BEGINDATA
   11111AAAAAAAAAA
   22222BBBBBBBBBB Is this the right approach with the construct after the POSITION statement?  How would this work?

Many thanks,
Brian Fenske
brianf_at_purpleinc.com Received on Wed Jun 18 1997 - 00:00:00 CDT