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 '\t' 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 '\t' 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 '\t'
                  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

can you provide an example of two records in the dat file ? it is text or binary ? — Roberto Hernandez, Oct 04 '21 at 13:02
" 31234569999999 §0 §A §X §0 § §GGGG" <- not working, but if I replace field with only one whitespace to e.g. Z it works: " 31234569999999 §0 §A §X §0 §Z §GGGG" — FFF, Oct 04 '21 at 13:07
For me it looks that LTRIM(" ") in 19c causing that field mapping is messed up — FFF, Oct 04 '21 at 13:13
I would like to reproduce your issue, but I need the table ddl, some sample data and the complete external table ddl. I have Oracle 19c and 11g available. — Roberto Hernandez, Oct 04 '21 at 15:57
I've added test table definition with example dataset (working and not-working new db). Please let me know if you are able to reproduce — FFF, Oct 05 '21 at 11:08
@RobertoHernandez I edited question and added details you asked for, let me know if you need anything else — FFF, Oct 07 '21 at 06:48
I post an answer with my demo, and in my case works either with blank spaces or without them — Roberto Hernandez, Oct 07 '21 at 08:04

score 1 · Answer 1 · answered Oct 05 '21 at 06:39

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

Could you replace value e.g. C4 from "X" to " " (one whitespace) in TEST_FILE.dat and check if it's working? Which version you are running at? — FFF, Oct 05 '21 at 11:13
The problem is that old and new db have the same definition with LTRIM. Old one correctly returns empty field, but new one is rejecting whole line with KUP-04023 — FFF, Oct 06 '21 at 08:30

Roberto Hernandez · Accepted Answer · 2021-10-07T12:43:07.260

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 '\t' 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 '\t' 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 '\t' 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 '\t' 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.

Thanks for your testing. The problem is, that we don't want to change file delimiter. We just want to migrate db from 11g (where current delimiter works, we are not getting error, all rows are returned, C field as NULL) to 19c without or minimum changes. Current delimiter is tabulator - I pasted unix output with :set list so it is shows as ^I (sorry, maybe it was not clear), but data is delimited by tab. Could you try it again on 11g and 19c using '\t' in DDL definition and having record: NAME1\t0\t \tUK — FFF, Oct 07 '21 at 10:14
Ah ok, well I was guessing by your data input that the delimiter was `'^'` . Let me try again — Roberto Hernandez, Oct 07 '21 at 10:49
I added DEMO in my question, hope it shows clearly issue we are facing — FFF, Oct 07 '21 at 10:54
I updated the answer, and in my case works in both cases, when I want the null in the table, or when I want the null removed — Roberto Hernandez, Oct 07 '21 at 11:07
But you still adding 'I' in input data which is not there. Column C in file should be ' ' (one whitespace). ^I should be replaced with \t. Please check my `cat -A TEST.dat` output in DEMO, you should have the same. — FFF, Oct 07 '21 at 11:27
Sorry. Update post with your own data. I used `NOTRIM` to get the same output as you in 11g, loading the third field as null. But if you use `LDRTRIM` it works as well — Roberto Hernandez, Oct 07 '21 at 11:39
But if you use LTRIM - are you also getting different results on 11g and 19c? I know that I can run query without LTRIM - but the problem is LTRIM is intentional parameter as we are getting files with not needed whitespace on LEFT side...we just want to upgrade db and avoid additional changes in db structure....even if we remove LTRIM - we won't get same query output as on 11g, because of not needed whitespaces - files are provided by different systems, so also cannot easily fix them — FFF, Oct 07 '21 at 12:20
@FFF, I would appreciate if you can upvote the answer, it is the best way to say thanks in SO. I am going to test with LTRIM, — Roberto Hernandez, Oct 07 '21 at 12:22
post updated. Clearly for me it is a bug in 11g. It should not work. Since 12c the treatment of whole empty fields with blank spaces works from right to left, that is why it works if you use RTRIM. — Roberto Hernandez, Oct 07 '21 at 12:44
Thanks a lot for your help. Just one additional question, if first value in file is " NAME1" (so have couple of whitespaces before value) - any idea if it's possible to remove it while reading from external table? — FFF, Oct 07 '21 at 12:57

External table read issue on 19c

2 Answers2