How to load multiple rows into single column in SQL Loader?

Option 1

Sample Table Script

CREATE TABLE XX_SQLLDR_DEMO_STG

(

   RECORD_ID   NUMBER,

   SENDER      VARCHAR2 (120),

   RECEIVER    VARCHAR2 (120),

   TEXT        VARCHAR2 (2000)

);

Sample Control File

LOAD DATA
CHARACTERSET UTF8
INFILE '$DATA_FILE_NAME' CONTINUEIF LAST <> '|'
INTO TABLE XX_SQLLDR_DEMO_STG
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORD_ID "LTRIM(RTRIM(:RECORD_ID))",
SENDER    "LTRIM(RTRIM(:SENDER))",
RECEIVER  "LTRIM(RTRIM(:RECEIVER))",
TEXT      "LTRIM(RTRIM(:TEXT))"

)

Sample Data File

Need to Add the Data / Fields delimiter at the end of each record in Data File.


1|ABCD@XYZ.COM|XYZ@ABCD.COM|Hi XYZ,

how to load Multiple lines as single record in sqlldr?

Thanks & Regards,
ABCD|
2|XYZ@ABCD.COM|ABCD@XYZ.COM|Hi ABCD,

This is test data file for multiple lines loading into a single field.

Thanks & Regards,
xyz|


It will loads the data (replacing newline character) like this...

Option 2

Sample Control File

LOAD DATA
CHARACTERSET UTF8
INFILE '$DATA_FILE_NAME' "STR '~!~'"
INTO TABLE XX_SQLLDR_DEMO_STG
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORD_ID "LTRIM(RTRIM(:RECORD_ID))",
SENDER    "LTRIM(RTRIM(:SENDER))",
RECEIVER  "LTRIM(RTRIM(:RECEIVER))",
TEXT      "LTRIM(RTRIM(:TEXT))"

)

Sample Data File

Need to Add the Data / Fields delimiter with unused string (like this "~!~") at the end of each record in Data File.


1|ABCD@XYZ.COM|XYZ@ABCD.COM|Hi XYZ,

how to load Multiple lines as single record in sqlldr?

Thanks & Regards,
ABCD|~!~
2|XYZ@ABCD.COM|ABCD@XYZ.COM|Hi ABCD,

This is test data file for multiple lines loading into a single field.

Thanks & Regards,
xyz|~!~


It will loads the data as expected...