Wrong Interpretation Of Record Delimiters, Oracle Treat X'7C0A' And X'7C20' As Identical While Loading Data Into External Table (Doc ID 2559117.1)

Last updated on JULY 20, 2024

Applies to:

Goal

 Wrong Interpretation Of Record Delimiters, Oracle Treat X'7C0A' And X'7C20' As Identical While Loading Data Into External Table.

SQL> CREATE OR REPLACE DIRECTORY TEST_DIR AS '/refresh/home/';

Directory created.

SQL> Drop Table EX_ISSEXTENSION_BAD;
Drop Table EX_ISSEXTENSION_BAD
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table EX_ISSEXTENSION_BAD
(
  extensionid   VARCHAR2(25),
  referencetype VARCHAR2(2),
  referenceid   VARCHAR2(25),
  appliesto     VARCHAR2(1),
  fieldnumber   NUMBER,
  fieldvalue    VARCHAR2(255)
  2    3    4    5    6    7    8    9  )
organization external
(
  type ORACLE_LOADER
  default directory TEST_DIR
  access parameters
  (
    Records Delimited By X'7C0A'
    CharacterSet AL32UTF8
    Fields Terminated By '|'
    Optionally Enclosed By '"'
    Missing Field Values Are Null
    (
    extensionid Char RTrim, referencetype Char RTrim, referenceid Char RTrim,
appliesto Char RTrim, fieldnumber, fieldvalue Char RTrim
    )
  )
  location (TEST_DIR:'externalfile.ext')
)
reject limit UNLIMITED; 10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28

Table created.

SQL> Select Count(*) From EX_ISSEXTENSION_BAD;

  COUNT(*)
----------
         2

SQL> Select * From EX_ISSEXTENSION_BAD;

EXTENSIONID               RE REFERENCEID               A FIELDNUMBER
------------------------- -- ------------------------- - -----------
FIELDVALUE
--------------------------------------------------------------------------------
158                       A  5783                      A          24
VICTOR3 MA
DRIZ

158                       A  5783                      A          24
VICTOR4 MADRIZ


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
-bash-4.1$ ls -ltr
total 12248
-rw-rw-r-- 1 oracle oracle     131 May 29 14:08 externalfile.ext
-rw-r--r-- 1 oracle oracle    2872 May 29 14:09 EX_ISSEXTENSION_BAD_15230.log
-rw-r--r-- 1 oracle oracle      68 May 29 14:09 EX_ISSEXTENSION_BAD_15230.bad
-bash-4.1$ cat EX_ISSEXTENSION_BAD_15230.log


 LOG file opened at 05/29/19 14:09:25

Field Definitions for table EX_ISSEXTENSION_BAD Record format DELIMITED, delimited by 7C0A Data in file has same endianness as the platform Rows with all null fields are accepted Fields in Data Source:

    EXTENSIONID                     CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
    REFERENCETYPE                   CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
    REFERENCEID                     CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
    APPLIESTO                       CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
    FIELDNUMBER                     CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    FIELDVALUE                      CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
KUP-04021: field formatting error for field FIELDVALUE
KUP-04101: record 1 rejected in file /refresh/home/externalfile.ext
KUP-04021: field formatting error for field FIELDVALUE
KUP-04101: record 2 rejected in file /refresh/home/externalfile.ext
KUP-04021: field formatting error for field EXTENSIONID
KUP-04101: record 5 rejected in file /refresh/home/externalfile.ext


 LOG file opened at 05/29/19 14:09:35

Field Definitions for table EX_ISSEXTENSION_BAD
  Record format DELIMITED, delimited by 7C0A
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    EXTENSIONID                     CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
    REFERENCETYPE                   CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
    REFERENCEID                     CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
    APPLIESTO                       CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
    FIELDNUMBER                     CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    FIELDVALUE                      CHAR (255)
      Terminated by "|"
      Enclosed by """ and """
      Trim whitespace from right
KUP-04021: field formatting error for field FIELDVALUE
KUP-04101: record 1 rejected in file /refresh/home/externalfile.ext
KUP-04021: field formatting error for field FIELDVALUE
KUP-04101: record 2 rejected in file /refresh/home/externalfile.ext
KUP-04021: field formatting error for field EXTENSIONID
KUP-04101: record 5 rejected in file /refresh/home/externalfile.ext
-bash-4.1$


Tried using the combination of chr(124) chr(10) which are equal to | and \n.


SQL> create table EX_ISSEXTENSION_BAD
  2  (
  3    extensionid   VARCHAR2(25),
  4    referencetype VARCHAR2(2),
  5    referenceid   VARCHAR2(25),
  6    appliesto     VARCHAR2(1),
  7    fieldnumber   NUMBER,
  8    fieldvalue    VARCHAR2(255)
  9  )
 10  organization external
 11  (
 12    type ORACLE_LOADER
 13    default directory TEST_DIR
 14    access parameters
 15    (
 16      Records Delimited By CHR(124)||CHR(10)
 17      CharacterSet AL32UTF8
 18      Fields Terminated By '|'
    Optionally Enclo 19  sed By '"'
 20      Missing Field Values Are Null
 21      (
 22      extensionid Char RTrim, referencetype Char RTrim, referenceid Char RTrim,
 23  appliesto Char RTrim, fieldnumber, fieldvalue Char RTrim
 24      )
 25    )
 26    location (TEST_DIR:'externalfile.ext')
 27  )
 28  reject limit UNLIMITED;

Table created.

SQL> Select Count(*) From EX_ISSEXTENSION_BAD;
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of:
"double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01008: the bad identifier was: CHR
KUP-01007: at line 1 column 22

Tested in SQL*Loader and it is working fine if we are using '|/n' as delimiters.
Tested on 12.2 & 18.5.
you look on line 3

 84|A|5743|A|24| VICTOR1 MADRIZ|
 158|A|5783|A|24| VICTOR2 MADRIZ|
 158|A|5783|A|24|VICTOR3 MA
 DRIZ|
 158|A|5783|A|24|VICTOR4 MADRIZ|

You would see that INSIDE line there is /n delimiter. And if we use it the line would be interpreted incorrectly.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.