SQL & PL/SQL

Hi all,

The CSV standards say that "Fields that contain a special character (comma, newline, or double quote), must be enclosed in double quotes. If a field's value contains a double quote character it is escaped by placing another double quote character next to it".

I'm trying to create an external table that accepts a csv file, in which some fields may have newlines.

CREATE TABLE CSV_EXTERNAL
   (field1 VARCHAR2(50), 
    field2 VARCHAR2(50)) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "TEMP"
      ACCESS PARAMETERS
      (records delimited by newline
       fields terminated by ';' optionally enclosed by '"'
       missing field VALUES are NULL )
      LOCATION
       ( 'test.csv')); 
{code}

When I'm using a file like this:
{code}
"AAAAA";"This is a test"
"BBBBB";"This is; a test 2"
"CCCCC";"This is , ""a"" test ""3"""
{code}

Everything works properly. The comma, the semicolon and the the double quotes are accepted properly.

However when I try to add a newline in some of the fields, the external table won't accept it:
{code}
"AAAAA";"This is a 
test"
"BBBBB";"This is; a test 2"
"CCCCC";"This is , ""a"" test ""3"""
{code}

Although if I open this file in excel for example, it works properly.

Is there anyway to make this external table to support the CSV files specifications?

Thanks in advance,
Manuel Vidigal