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