External table read issue on 19c

Question

we are migrating DB from Oracle 11g -> 19 and facing issue with external table. Old and new db have exactly same table definition and pointing to the same file (db running on different hosts but pointing same qtree). Old DB can query file without errors, but new one rejecting all rows with: KUP-04023: field start is after end of record

Tables have below config:

CREATE TABLE TEST
(
    AA    VARCHAR2 (40 BYTE),
    BB    VARCHAR2 (2 BYTE),
    CC    VARCHAR2 (3 BYTE),
    DD    VARCHAR2 (12 BYTE)
)
ORGANIZATION EXTERNAL
    (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY TEST_DIRECTORY
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
            BADFILE TEST_DIRECTORY : 'TEST.bad'
            LOGFILE TEST_DIRECTORY : 'TEST.log'
            FIELDS
                TERMINATED BY '	' LTRIM REJECT ROWS WITH ALL NULL FIELDS
            (AA,
             BB,
             CC,
             DD))
        LOCATION (TEST_DIRECTORY:'TEST.dat'))
    REJECT LIMIT UNLIMITED;

Test data (replace ^I with tabulator):

NAME1^I0^I ^IUK
NAME2^I0^I ^IUS

When I removed LTRIM, all data is read on new DB (but we need to keep LTRIM as input files contain unnecessary spaces). I've noticed that one field has value of one space and it looks to be causing that issue, but why only on new database? Any ideas what is the reason or how to easily fix?

NLS db/session parameters are same on both databases...but maybe there is some global parameter which could cause this issue?

Test data manually updated which is working on both db (replace whitespace in third column with X)

NAME1^I0^IX^IUK
NAME2^I0^IX^IUS

DEMO:

Below table created on 11g and 19c:

CREATE TABLE TEST
(
    AA    VARCHAR2 (40 BYTE),
    BB    VARCHAR2 (2 BYTE),
    CC    VARCHAR2 (3 BYTE),
    DD    VARCHAR2 (12 BYTE)
)
ORGANIZATION EXTERNAL
    (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY TEST_DIRECTORY
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
            BADFILE TEST_DIRECTORY : 'TEST.bad'
            LOGFILE TEST_DIRECTORY : 'TEST.log'
            FIELDS 
                TERMINATED BY '	' LTRIM
                  REJECT ROWS WITH ALL NULL FIELDS
            (AA,
             BB,
             CC  ,
             DD))
        LOCATION (TEST_DIRECTORY:'TEST.dat'))
    REJECT LIMIT UNLIMITED;

Both tables sourcing same file TEST.dat (data delimited by tabulator which is shown as 2 characters ^I):

$ cat -A TEST.dat
NAME1^I0^I ^IUK$
NAME2^I0^I ^IUS$

Querying on 11g:

SQL> SELECT * FROM TEST;

AA                                       BB CC  DD
---------------------------------------- -- --- ------------
NAME1                                    0      UK
NAME2                                    0      US

SQL> SELECT dump(CC) FROM TEST;

DUMP(CC)
--------------------------------------------------------------------------------
NULL
NULL

Querying on 19c:

SQL> SELECT * FROM TEST;

no rows selected

TEST.log shows after running query on 19c:

Bad File: TEST.bad

Field Definitions for table TEST
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source:

    AA                              CHAR (255)
      Terminated by "   "
      Trim whitespace from left
    BB                              CHAR (255)
      Terminated by "   "
      Trim whitespace from left
    CC                              CHAR (255)
      Terminated by "   "
      Trim whitespace from left
    DD                              CHAR (255)
      Terminated by "   "
      Trim whitespace from left
KUP-04021: field formatting error for field DD
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /home/fff/TEST.dat
KUP-04021: field formatting error for field DD
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file /home/fff/TEST.dat

Then, I recreated tables on both db just without LTRIM:

CREATE TABLE TEST
(
    AA    VARCHAR2 (40 BYTE),
    BB    VARCHAR2 (2 BYTE),
    CC    VARCHAR2 (3 BYTE),
    DD    VARCHAR2 (12 BYTE)
)
ORGANIZATION EXTERNAL
    (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY TEST_DIRECTORY
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
            BADFILE TEST_DIRECTORY : 'TEST.bad'
            LOGFILE TEST_DIRECTORY : 'TEST.log'
            FIELDS 
                TERMINATED BY '	'
                  REJECT ROWS WITH ALL NULL FIELDS
            (AA,
             BB,
             CC  ,
             DD))
        LOCATION (TEST_DIRECTORY:'TEST.dat'))
    REJECT LIMIT UNLIMITED;

Querying on new table in 11g:

SQL> SELECT * FROM TEST;

AA                                       BB CC  DD
---------------------------------------- -- --- ------------
NAME1                                    0      UK
NAME2                                    0      US

SQL> SELECT dump(CC) FROM TEST;

DUMP(CC)
--------------------------------------------------------------------------------
Typ=1 Len=1: 32
Typ=1 Len=1: 32

Querying on new table in 19c:

SQL> SELECT * FROM TEST;

AA                                       BB CC  DD
---------------------------------------- -- --- ------------
NAME1                                    0      UK
NAME2                                    0      US

SQL> SELECT dump(CC) FROM TEST;

DUMP(CC)
--------------------------------------------------------------------------------
Typ=1 Len=1: 32
Typ=1 Len=1: 32

user_7367293 · Answer

Its not LTRIM its LDRTRIM.

SQL> create table et
  2  ( c1 varchar2(16),
  3    c2 varchar2(8),
  4    c3 varchar2(8),
  5    c4 varchar2(8),
  6    c5 varchar2(8),
  7    c6 varchar2(8),
  8    c7 varchar2(8)
  9  )
 10  ORGANIZATION EXTERNAL
 11    (  TYPE ORACLE_LOADER
 12       DEFAULT DIRECTORY temp
 13       ACCESS PARAMETERS
 14         ( RECORDS DELIMITED BY NEWLINE
 15          BADFILE temp: 'TEST_FILE.bad'
 16          LOGFILE temp: 'TEST_FILE.log'
 17          FIELDS TERMINATED BY X'20A7' LTRIM
 18          REJECT ROWS WITH ALL NULL FIELDS
 19         (
 20  c1,c2,c3,c4,c5,c6,c7
 21  )                   )
 22       LOCATION (temp:'TEST_FILE.dat')
 23    )
 24  REJECT LIMIT UNLIMITED;

Table created.

SQL>
SQL> select * from et;

C1               C2       C3       C4       C5       C6       C7
---------------- -------- -------- -------- -------- -------- --------
31234569999999   0        A        X        0        Z        GGGG

SQL>
SQL> drop table et;

Table dropped.

SQL>
SQL> create table et
  2  ( c1 varchar2(16),
  3    c2 varchar2(8),
  4    c3 varchar2(8),
  5    c4 varchar2(8),
  6    c5 varchar2(8),
  7    c6 varchar2(8),
  8    c7 varchar2(8)
  9  )
 10  ORGANIZATION EXTERNAL
 11    (  TYPE ORACLE_LOADER
 12       DEFAULT DIRECTORY temp
 13       ACCESS PARAMETERS
 14         ( RECORDS DELIMITED BY NEWLINE
 15          BADFILE temp: 'TEST_FILE.bad'
 16          LOGFILE temp: 'TEST_FILE.log'
 17          FIELDS TERMINATED BY X'20A7' LDRTRIM
 18          REJECT ROWS WITH ALL NULL FIELDS
 19         (
 20  c1,c2,c3,c4,c5,c6,c7
 21  )                   )
 22       LOCATION (temp:'TEST_FILE.dat')
 23    )
 24  REJECT LIMIT UNLIMITED;

Table created.

SQL>
SQL> select * from et;

C1               C2       C3       C4       C5       C6       C7
---------------- -------- -------- -------- -------- -------- --------
 31234569999999  0        A        X        0                 GGGG
 31234569999999  0        A        X        0        Z        GGGG

user_13755538 · Answer

Let me try to reproduce your issue on my own environment

Using Oracle 19c on Red Hat Linux 7.2

SQL> select version from v$instance ;

VERSION
-----------------
19.0.0.0.0

Demo

Update: delimiter is tab

Content of the file

$ cat -A TEST.dat
NAME1^I0^I ^IUK$
NAME2^I0^I ^IUS$

External Table

SQL> drop table TEST_EXTERNAL_TABLE ;

Table dropped.

SQL> CREATE TABLE TEST_EXTERNAL_TABLE
  2  (
  3      AA    VARCHAR2 (40 BYTE),
  4      BB    VARCHAR2 (2 BYTE),
  5      CC    VARCHAR2 (3 BYTE),
  6      DD    VARCHAR2 (12 BYTE)
  7  )
  8  ORGANIZATION EXTERNAL
  9      (
 10          TYPE ORACLE_LOADER
 11          DEFAULT DIRECTORY DIR_TEST
 12          ACCESS PARAMETERS (
 13              RECORDS DELIMITED BY NEWLINE
 14              BADFILE DIR_TEST : 'TEST.bad'
 15              LOGFILE DIR_TEST : 'TEST.log'
 16              FIELDS TERMINATED BY '	' NOTRIM
 17                     REJECT ROWS WITH ALL NULL FIELDS
 18              (AA,
 19               BB,
 20               CC,
 21               DD))
 22*         LOCATION (DIR_TEST:'TEST.dat'))
SQL> /

Table created.

SQL>  select * from TEST_EXTERNAL_TABLE ;

AA                                       BB CC  DD
---------------------------------------- -- --- ------------
NAME1                                    0      UK
NAME2                                    0      US

SQL> select dump(cc) from TEST_EXTERNAL_TABLE ;

DUMP(CC)
--------------------------------------------------------------------------------
Typ=1 Len=1: 32
Typ=1 Len=1: 32

In my case I am able to load, but the blank spaces remain in the field, which is the expected behaviour of NOTRIM vs LDRTRIM.

LDRTRIM is used to provide compatibility with SQL*Loader trim features. It is the same as NOTRIM except in the following cases:

If the field is not a delimited field, then spaces will be trimmed from the right. If the field is a delimited field with OPTIONALLY ENCLOSED BY specified, and the optional enclosures are missing for a particular instance, then spaces will be trimmed from the left.

Doing the same with LDRTRIM

SQL> drop table TEST_eXTERNAL_TABLE;

Table dropped.

SQL> l
  1  CREATE TABLE TEST_EXTERNAL_TABLE
  2  (
  3      AA    VARCHAR2 (40 BYTE),
  4      BB    VARCHAR2 (2 BYTE),
  5      CC    VARCHAR2 (3 BYTE),
  6      DD    VARCHAR2 (12 BYTE)
  7  )
  8  ORGANIZATION EXTERNAL
  9      (
 10          TYPE ORACLE_LOADER
 11          DEFAULT DIRECTORY DIR_TEST
 12          ACCESS PARAMETERS (
 13              RECORDS DELIMITED BY NEWLINE
 14              BADFILE DIR_TEST : 'TEST.bad'
 15              LOGFILE DIR_TEST : 'TEST.log'
 16              FIELDS TERMINATED BY '	' LDRTRIM
 17                     REJECT ROWS WITH ALL NULL FIELDS
 18              (AA,
 19               BB,
 20               CC,
 21               DD))
 22*         LOCATION (DIR_TEST:'TEST.dat'))
SQL> /

Table created.

SQL> select * from TEST_EXTERNAL_TABLE ;

AA                                       BB CC  DD
---------------------------------------- -- --- ------------
NAME1                                    0      UK
NAME2                                    0      US

SQL> select dump(cc) from TEST_EXTERNAL_TABLE ;

DUMP(CC)
--------------------------------------------------------------------------------
Typ=1 Len=1: 32
Typ=1 Len=1: 32

SQL>

If you use LTRIM it does not work, because the white spaces are in the right side, as the field is empty. That is the default behaviour, at least since 12c is how it works and should be.

SQL> drop table TEST_EXTERNAL_TABLE ;

Table dropped.

SQL> CREATE TABLE TEST_EXTERNAL_TABLE
(
    AA    VARCHAR2 (40 BYTE),
  2    3    4      BB    VARCHAR2 (2 BYTE),
    CC    VARCHAR2 (3 BYTE),
  5    6      DD    VARCHAR2 (12 BYTE)
  7  )
  8  ORGANIZATION EXTERNAL
    (
  9   10          TYPE ORACLE_LOADER
        DEFAULT DIRECTORY DIR_TEST
        ACCESS PARAMETERS (
 11   12   13              RECORDS DELIMITED BY NEWLINE
            BADFILE DIR_TEST : 'TEST.bad'
            LOGFILE DIR_TEST : 'TEST.log'
 14   15   16              FIELDS TERMINATED BY '	' LTRIM
                        REJECT ROWS WITH ALL NULL FIELDS
            (AA,
             BB,
 17   18   19   20               CC,
             DD))
        LOCATION (DIR_TEST:'TEST.dat'))
 21   22   23      REJECT LIMIT UNLIMITED;

Table created.

SQL> select * from TEST_EXTERNAL_TABLE ;

no rows selected

Now with RTRIM works as expected, because the whitespaces in the whole field are treated from right to left.

SQL> drop table TEST_EXTERNAL_TABLE ;

Table dropped.

SQL> CREATE TABLE TEST_EXTERNAL_TABLE
  2  (
    AA    VARCHAR2 (40 BYTE),
  3    4      BB    VARCHAR2 (2 BYTE),
    CC    VARCHAR2 (3 BYTE),
    DD    VARCHAR2 (12 BYTE)
  5    6    7  )
ORGANIZATION EXTERNAL
    (
  8    9   10          TYPE ORACLE_LOADER
 11          DEFAULT DIRECTORY DIR_TEST
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
 12   13   14              BADFILE DIR_TEST : 'TEST.bad'
            LOGFILE DIR_TEST : 'TEST.log'
 15   16              FIELDS TERMINATED BY '	' RTRIM
 17                     REJECT ROWS WITH ALL NULL FIELDS
 18              (AA,
       19         BB,
 20               CC,
             DD))
        LOCATION (DIR_TEST:'TEST.dat'))
 21   22   23      REJECT LIMIT UNLIMITED;

Table created.

SQL> select * from TEST_EXTERNAL_TABLE ;

AA                                       BB CC  DD
---------------------------------------- -- --- ------------
NAME1                                    0      UK
NAME2                                    0      US

My advice: Use LDRTRIM, or even better, avoid whitespaces all together is that is an option. Regarding your test in 11g, well that is quite an old version and probably the behaviour is consequence of a bug, although I could not find any reported one explaining this behaviour.

External table read issue on 19c

2 Answers