SQL & PL/SQL

Hi,

I am trying to load data using External tables. I use the following code to create my table
drop table xtern_empl_rpt;
create table xtern_empl_rpt
( empl_id varchar2(3),
last_name varchar2(50),
first_name varchar2(50),
ssn varchar2(9),
email_addr varchar2(100),
years_of_service number(2,0)
)
organization external
( default directory xtern_data_dir
access parameters
( records delimited by newline
SKIP 1
FIELDS TERMINATED BY ','
optionally enclosed by '"'
missing field values are null
)
location ('a1.csv')
);


This works fine, but if a line contains a carriage return I get the following error:

KUP-04021: field formatting error for field FIRST_NAME
KUP-04036: second enclosing delimiter not found


Here is the data file I am trying to load
1 Hutt Jab243ba 896743856 jabba@thecompany.com 1
1 Hutt "TE
st" 896743856 jabba@thecompany.com 1


Any ideas of how to get around this? Can I replace the carriage return with a space ?

Any ideas would be greatly appreciated!