4

I got a strange behaviour with external tables with unicode characters in 18c which didn't occur before in 12.2

As testcase I created a file with a unicode character + ABCD:

declare  
   v_file utl_file.file_type;  
   v_bas varchar2(20):='ABCD';  
begin  
  
   v_file := utl_file.fopen('ORDER_ENTRY', 'datei.csv', 'W');  
   utl_file.put_line(v_file, '12;'||unistr('\C2B0')||v_bas);  
   utl_file.FFLUSH(v_file);  
   utl_file.fclose(v_file);  
end;  
/  

To read the data I created 3 different external tables:

CREATE TABLE system.test_datei_csv_wep1  
   (    rn number(10),  
      col1 VARCHAR2(10 CHAR)  
   )   
   ORGANIZATION EXTERNAL   
    ( TYPE ORACLE_LOADER  
      DEFAULT DIRECTORY "ORDER_ENTRY"  
      ACCESS PARAMETERS  
       ( RECORDS DELIMITED BY '\n'  
            STRING SIZES ARE IN CHARACTERS  
            CHARACTERSET 'WE8ISO8859P1'  
            TERRITORY 'GERMANY'  
            NODISCARDFILE  
            FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'  
            MISSING FIELD VALUES ARE NULL  
            (    rn char(10),  
            col1    CHAR (10)   )  
         )  
      LOCATION  
       ( 'datei.csv'     )  
    );    

2nd:

CREATE TABLE system.test_datei_csv_utf8  
(    rn number(10),  
  col1 VARCHAR2(10 CHAR)  
)   
ORGANIZATION EXTERNAL   
( TYPE ORACLE_LOADER  
  DEFAULT DIRECTORY "ORDER_ENTRY"  
  ACCESS PARAMETERS  
   ( RECORDS DELIMITED BY '\n'  
        STRING SIZES ARE IN CHARACTERS  
        CHARACTERSET 'UTF8'  
        TERRITORY 'GERMANY'  
        NODISCARDFILE  
        FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'  
        MISSING FIELD VALUES ARE NULL  
        (    rn char(10),  
       col1    CHAR (200)   )  
     )  
  LOCATION  
   ( 'datei.csv')  
);   

3rd:

    CREATE TABLE system.test_datei_csv_utf8_in_byte  
   (    rn number(10),  
      col1 VARCHAR2(10 CHAR)  
   )   
   ORGANIZATION EXTERNAL   
    ( TYPE ORACLE_LOADER  
      DEFAULT DIRECTORY "ORDER_ENTRY"  
      ACCESS PARAMETERS  
       ( RECORDS DELIMITED BY '\n'  
            STRING SIZES ARE IN bytes  
            CHARACTERSET 'UTF8'  
            TERRITORY 'GERMANY'  
            NODISCARDFILE  
            FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'  
            MISSING FIELD VALUES ARE NULL  
            (    rn char(10),  
           col1    CHAR (10)   )  
         )  
      LOCATION  
       ( 'datei.csv'  
       )  
    );    

As result for selects I get on 12.2:

SQL> select * from system.test_datei_csv_wep1;

RN COL1
12 슰ABCD

SQL> select * from system.test_datei_csv_utf8;

RN COL1
12 슰ABCD

SQL> select * from system.test_datei_csv_utf8_in_byte;

RN COL1
12 슰ABCD

which is fine. But on 18c I get:

SQL> select * from system.test_datei_csv_wep1;

RN COL1
12 ì°ABCD

SQL> select * from system.test_datei_csv_utf8;

RN COL1
12 슰AB

SQL> select * from system.test_datei_csv_utf8_in_byte;

RN COL1
12 슰ABCD

In the result for the default external table setup (STRING SIZES ARE IN CHARACTERS is default) in system.test_datei_csv_utf8 CD is missing. Anyone knows if the new behavoiur is correct?

Best regards Thomas

Community
  • 1
  • 1
Thomas Strub
  • 1,275
  • 7
  • 20

1 Answers1

0

Got a bug fix from oracle. Will probably be included in the next RU.

Thomas Strub
  • 1,275
  • 7
  • 20
  • Thanks for this nice analysis. I just ran into the same problem. Glad to use byte sizes instead of character sizes as workaround. What Oracle version contains the fix for this issue? – Thomas Andres Nov 05 '19 at 13:25
  • We had to ask for a patch (29713810) for 19.4. https://updates.oracle.com/download/29713810.html – Thomas Strub Nov 05 '19 at 15:34