How to include commas in Oracle external table

Question

I have a pipe delimited file that contains commas on the very last field like so:

COLOR|CAT|CODES
Red|Pass|tiger, 12@fol, letmein
Blue|Pass|jkd@332, forpw, wonton
Gray|Pass|rochester, tommy, 23$ai,

I terminate the last column by whitespace, and everything works out good with no errors, except that it will only include/read the first value and first comma in the last column e.g. tiger, jkd@332, etc. Obviously because of the whitespace after the comma.

How do I include the commas without getting any errors? I have tried " ", /r, /n, /r/n and even excluding the "terminated by" in the last column, and while those will work to include the commas, I will get the ORA-29913 and ORA-30653 reject error every time I select all from the external table (contains thousands of records).

I have the reject limit to 10, but I don't want to change it to UNLIMITED because I don't want to ignore those errors, also I cannot change the file.

My code:

    --etc..
    FIELDS TERMINATED BY '|'
            OPTIONALLY ENCLOSED BY '"'
            MISSING FIELD VALUES ARE NULL
    --etc..
    CODES CHAR TERMINATED BY WHITESPACE

Littlefoot Littlefoot · Accepted Answer · 2020-06-12T19:52:23

Here's how:

SQL> create table color (
  2    color      varchar2(5),
  3    cat        varchar2(5),
  4    codes      varchar2(50)
  5  )
  6  organization external (
  7    type oracle_loader
  8    default directory ext_dir
  9    access parameters (
 10      records delimited by newline
 11      skip 1
 12      fields terminated by '|'
 13      missing field values are null
 14      (
 15        color    char(5),
 16        cat      char(5),
 17        codes    char(50)
 18      )
 19    )
 20    location ('color.txt')
 21  )
 22  parallel 5
 23  reject limit unlimited;
SQL>
SQL> select * From color;

COLOR CAT   CODES
----- ----- --------------------------------------------------
Red   Pass  tiger, 12@fol, letmein
Blue  Pass  jkd@332, forpw, wonton
Gray  Pass  rochester, tommy, 23$ai,
SQL>

How to include commas in Oracle external table

1 Answers