Special Character not repeated not twice in Oracle

Question

I have a column Value Méroné in my Oracle DB. We are writing it to a csv file using utl_file package.Since this value has a special character we have used convert function to change the character coding to remove the junk character while writing. So the convert function goes like this- convert(REC.DT25, 'WE8DEC'). But the problem now is that the value is coming as only Méron and the last character is missing. I have tried everything from changing it to different character encoding, but still no luck. Could you please help? The code is as follows CREATE OR REPLACE PROCEDURE SAMPLE_MERONE AS

CREATE OR REPLACE PROCEDURE SAMPLE_MERONE AS 

CURSOR C1 IS select * from gsal_mosaic_prf_output where CS46SIGFORMALNAME 
LIKE 'Lt. Jowens Méroné' AND ID_NUMBER='8-13678728';

MERONE_FILE UTL_FILE.FILE_TYPE;
V_MERONE_FILE VARCHAR2(300);

BEGIN
V_MERONE_FILE := 'MREONE_FILE.csv';
MERONE_FILE := UTL_FILE.FOPEN ( 'GSAL_PRF',V_MERONE_FILE,'w',32767) ;
IF UTL_FILE.IS_OPEN(MERONE_FILE) THEN
FOR REC IN C1
LOOP
UTL_FILE.PUT_LINE(MERONE_FILE,'"'||REC.ID_NUMBER||'","'||
REC.GROUP_ID||'","'||convert(REC.CS46LASTNAME,'WE8ISO8859P1', 
'UTF8')||'","'||
convert(REC.CS46SIGFORMALNAME,'WE8ISO8859P1', 
'UTF8')||'"',TRUE);
END LOOP;
UTL_FILE.FCLOSE ( MERONE_FILE ) ;
END IF;
END SAMPLE_MERONE;

Check if the two characters have the same byte values in your column. select dump(column) from table. They also need to have the correct byte values to encode that character in your database character set. — Rene, Nov 29 '17 at 10:37
What is your database character set? Please show the code where you use `UTL_FILE`. — Wernfried Domscheit, Nov 29 '17 at 11:30

Rene · Accepted Answer · 2017-11-29T13:31:02.160

Running the following script on a 12.2 AL32UTF8 database reproduces the issue.

declare

   cursor c1 is
      select lastname
            ,convert(lastname, 'WE8ISO8859P1','UTF8') convertedname
            ,dump(convert(lastname, 'WE8ISO8859P1','UTF8')) dumpconvertedname
        from (select 'Lt. Jowens Méroné' as lastname
                from dual);

   merone_file   utl_file.file_type;
   v_merone_file varchar2(300);

begin
   merone_file := utl_file.fopen('NGM1_PAD_IN', 'test.csv', 'w', 32767);
   if utl_file.is_open(merone_file)
   then
      for rec in c1
      loop
         dbms_output.put_line(rec.lastname ||' - ' ||rec.convertedname);     
         dbms_output.put_line(rec.dumpconvertedname); 
         utl_file.put_line(merone_file
                          ,rec.lastname || '","' ||
                           rec.convertedname || '"'
                          ,true);
      end loop;
      utl_file.fclose(merone_file);
   end if;
end;
/

Screen output:

Lt. Jowens MÃ©ronÃ© - Lt. Jowens Méron

Typ=1 Len=17: 76,116,46,32,74,111,119,101,110,115,32,77,233,114,111,110,233

File contents:

The conversion changes 195 169 into character 233. You can see it as the last character in the converted string. But somehow does not make it's way into the file.

Exploring the file with a hex editor confirms this.

As a workaround you can assemble all your data into a CLOB and write it as follows. This seems to convert your data correctly.

declare

   l_clob   clob;
   l_string varchar2(32767);

   cursor c1 is
      select lastname
        from (select 'Lt. Jowens Méroné' as lastname
                from dual);

begin

   dbms_lob.createtemporary(lob_loc => l_clob, cache => true, dur => dbms_lob.call);
   dbms_lob.open(lob_loc => l_clob, open_mode => dbms_lob.lob_readwrite);

   for rec in c1
   loop
      l_string := rec.lastname || '","' || rec.lastname || '"' || chr(13) || chr(10);
      dbms_lob.writeappend(lob_loc => l_clob, amount => length(l_string), buffer => l_string);

   end loop;

   dbms_xslprocessor.clob2file(flocation => 'NGM1_PAD_IN'
                              ,fname     => 'test2.csv'
                              ,cl        => l_clob
                              ,csid      => nls_charset_id('WE8ISO8859P1'));

end;
/

Special Character not repeated not twice in Oracle

1 Answers1