Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

External table with newline as terminator AND field value

821898Dec 7 2010 — edited Dec 7 2010

Hi all,

I've spreadly read the forum but not found a precise answer to my issue.

I've a CSV loaded as external table. Lines are terminated by '0d0a' (newline) and at the same time that chars are allowed values for field value. Unfortunately I cannot absolutely change these constraints.

CREATE TABLE EXT_TESTNEWLINE
(
F1 VARCHAR2(100 BYTE),
F2 VARCHAR2(100 BYTE),
F3 VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY CECEXT_TABLES
ACCESS PARAMETERS
( RECORDS DELIMITED BY *0x'0d0a'*
CHARACTERSET WE8ISO8859P1
NOBADFILE
NODISCARDFILE
NOLOGFILE
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION (CECEXT_TABLES:'testnewline.csv')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

The problem is that I cannot get a good loading, even using OPTIONALLY ENCLOSED BY '"'. I have this csv:
BOX;TEAM;DRIVER
1;DUCATI;BAYLISS
2;HONDA REPSOL;PEDROSA
3;YAMAHA RACING;SPIES

I insert now some newline:
BOX;TEAM;DRIVER
1;DUCATI;BAYLISS
2;"HONDA
REPSOL";PEDROSA
3;"YAMAHA
RACING";SPIES

Now If I perform a select I get this (can't format as code sorry!):

select * from EXT_TESTNEWLINE;

BOX - TEAM - DRIVER
1 - DUCATI - BAYLISS
REPSOL" - PEDROSA -
RACING" - SPIES -

How I can get ride of this?