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

ScrappyDev picture ScrappyDev · Feb 6, 2012 · Viewed 19.4k times · Source

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  
...

Answer

Xfox picture Xfox · Dec 23, 2020

You can specify the position of the fields:

load data
append
into table TABLE_NAME
(
FIELD1 POSITION(51:55),
FIELD2 POSITION(60:67),
FIELD3 EXPRESSION "CONSTANT_VALUE",
FIELD4 SEQUENCE,
FIELD5 EXPRESSION "current_timestamp(3)",
)

In the above example FIELD1 goes from char 51 to 55 (included), FIELD2 from 60 to 67.

FIELD3 is fixed to a value we want, FIELD4 generates an internal sequence and FIELD5 gets the current timestamp.