My Oracle Support Banner

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 02, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 18.5.0.0.0 to 19.3.0.0.0 [Release 18]
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


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