Search code examples
oraclesql-loaderfixed-widthfixed-length-record

How Do you load a fixed width data file using SQL*Loader which has no Line Delimiter but has fields with newline characters?

I need to load a flat file into an oracle database using SQL*Loader.
The flat file contains a field that contains newline characters as valid data.
The file is NOT line delimited by a newline character.

How would I Modify the following control file to do this?

LOAD DATA
 INFILE 'mydata.dat'
 INTO TABLE emp
 ( field1    POSITION(1:4)   INTEGER EXTERNAL,
   field2    POSITION(6:15)  CHAR,
   big_field POSITION(17:7000) CHAR
 )

Note: I have no control over the format of the incoming file. 

Note: ... indicates that the data continues to the end of the field  

example:  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
...  

result:  
field1: 1234  
field2: 67890abcde
big_field: ghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
1234567890abcdefghijklmnopqrstuvwxyz  
...
Solution

  • The problem here is that each line will be interpreted as a record in the flat file rather than a field. I am guessing that you may have to re format the file with a delimiter such as a comma or a tab or a pipe '|' with the '\n' so that the Loader interprets it as a one single record. Any occurrence of a new line '\n' will be treated as a new record I presume. refer to the link below-

    http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_concepts.htm#sthref718

    You cannot the format of the incoming file, but you can read that file and create a proper formatted mydata.dat for the SQL Loader. The whole point is to make the Loader understand 'what is your record terminator?', other wise your file would be like one huge record.