This discussion is archived
1 Reply Latest reply: Jan 29, 2013 11:29 AM by User286067 RSS

Help in External tables

943841 Newbie
Currently Being Moderated
Hi ,

I've a data file empdet.csv which is like this
empno,ename,eaddress,ezip
1,XXX,Flatno 322,50100
2,YYY,"Flatno 
",50101
3,ZZZ,Flatno 32,50102
Please notice the second record the data is split into next line and when I use external table then the second line is not rejected or discarded or neither loaded. External table is like this
CREATE TABLE empdet
( eno      number,
  ename    varchar2(10),
  eaddress varchar2(100),
  ezipcode number
 )
 ORGANIZATION EXTERNAL 
 ( TYPE ORACLE_LOADER
   DEFAULT DIRECTORY extdir
   ACCESS PARAMETERS
   ( 
     RECORDS DELIMITED BY NEWLINE
     SKIP 1
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LTRIM
     MISSING FIELD VALUES ARE NULL
     REJECT ROWS WITH ALL NULL FIELDS
   )
   LOCATION ('empdet.csv')
 )
 REJECT LIMIT UNLIMITED  
{code}

Any ideas how to load the second record, fixing the file itself is not an option considering huge volume of data.

Thanks                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points