Skip to Main Content

Load using External table fails in 18c

User_KEHP1Jul 2 2020 — edited Jul 3 2020

Hi

We have a file to be loaded on 18c database via external table. The file is downloaded in a Linux env and is accessed from there.

The load seems to be working fine on 12c database whereas it is failing on 18c. On analysis it is being found that  there is a BOM character () at the beginning of the file which is causing trouble. If this is removed from the file, it loads successfully. Any suggestions to make it work in 18c DB

The file is also converted to utf-8 encoding from utf-16le.

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

External Table

CREATE TABLE TEST_LOAD

(

  LEI                      VARCHAR2(4000 CHAR),

  LEGALNAME                VARCHAR2(4000 CHAR)

  )

ORGANIZATION EXTERNAL

  (  TYPE ORACLE_LOADER

     DEFAULT DIRECTORY LOAD_DIR

     ACCESS PARAMETERS

       ( records delimited by newline

     SKIP 1

        nodiscardfile

     FIELDS terminated by '|' OPTIONALLY ENCLOSED BY '"' LRTRIM

     MISSING FIELD VALUES ARE NULL

    (

  FNAME char(4000)

,LEGALNAME char(4000)

)

   )

     LOCATION (LOAD_DIR:'filename.csv')

  )

REJECT LIMIT 0

NOPARALLEL

NOMONITORING;

File Contents (header)

"FNAME "|"LEGALNAME"

Regards

Comments
Post Details
Added on Jul 2 2020
3 comments
72 views