How to read field with comma through Oracle external table

Question

I have a position separated text file which I have to read through Oracle external tables. In that position separated file I field as name separated by comma. For example:

123 abc,def 456. So I have to insert data into 3 columns. Fisrt column would have 123, second column would have abc,def and third column would have 456. In access parameter I have given "records delimited by newline". But while selecting data from external table, it only gives data before comma (abc). But I want to read abc,def. Can anybody help me with this?

BriteSponge BriteSponge · Accepted Answer · 2018-04-11T09:34:33

The following works for me;

CREATE TABLE test
(
  col_1 NUMBER,
  col_2 VARCHAR2(30),
  col_3 NUMBER
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY MY_DIR
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY '\r\n'
         FIELDS TERMINATED BY ' '
         OPTIONALLY ENCLOSED BY '"'
         MISSING FIELD VALUES ARE NULL
        ( col_1, col_2, col_3
         )
            )
     LOCATION (MY_DIR:'test.txt')
  )
REJECT LIMIT UNLIMITED;

Bear in mind that "records delimited by '/r/n'" may be specific to Windows but my results are as below;

SQL> select * from test
  2  /

 COL_1     COL_2                               COL_3
---------- ------------------------------ ----------

   123     ABC,DEF                               123
   789     ABCD,EF                               123
   456     A,B,C,DEF                             123

How to read field with comma through Oracle external table

1 Answers