organization external
(
  type ORACLE_LOADER
  default directory DIR_ALUMNI
  access parameters 
  (
    records delimited by NEWLINE
    fields terminated by ',' optionally enclosed BY '"' LDRTRIM
(
      ACTC_CODE_SA_NEW       char(500) TERMINATED BY ','  ENCLOSED BY '"',
      ACTC_CODE_VA_NEW       char(500) TERMINATED BY ','  ENCLOSED BY '"',

)

In order to load with embedded newlines - you will *need* to have control over the input file to a degree.

If you do not, you will not be using external tables nor sqlldr - they will not work.


There are four ways to do it:


o Load the data with some other character in the data that represents a newline (e.g., put the string \n in the text where a newline should appear) and use a SQL function to replace that text with a CHR(10) during load time.

o Use the FIX attribute on the INFILE directive, and load a fixed-length flat file. In this case there is no record terminator; – but rather, the fact that each record is exactly as long as every other record is used to determine where records begin and end.

o Use the VAR attribute on the INFILE directive, and load a variable-width file that uses a format such that the first few bytes of each line specify the length in bytes of the line to follow.

o Use the STR attribute on the INFILE directive to load a variable-width file with some sequence of characters that represents the end of line, as opposed to just the newline character representing this.



To demonstrate the STR one (the easiest to apply by far - you need to have an end of record terminator - I use "|\n" generally).

The create table would resemble:

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR(4000),
"LOC" VARCHAR(4000),
"COMMENTS" VARCHAR2(4000)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY 0x'7C0A' CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo.bad'
LOGFILE 't.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255)
TERMINATED BY ",",
"DNAME" CHAR(255)
TERMINATED BY ",",
"LOC" CHAR(255)
TERMINATED BY ",",
"COMMENTS" CHAR(255)
TERMINATED BY ","
)
)
location
(
'demo.dat'
)
)REJECT LIMIT UNLIMITED


and the data could look like:

10,xxx,here,"this is a
long field with
newlines"|
20,yyy,there,"this is a
long field with
newlines"|