Forum Stats

  • 3,828,121 Users
  • 2,260,865 Discussions
  • 7,897,483 Comments

Discussions

Convert clob data for german settings

Hi,

i have this database nls settings:

NLS_RDBMS_VERSION 12.2.0.1.0

NLS_NCHAR_CONV_EXCP FALSE

NLS_LENGTH_SEMANTICS BYTE

NLS_COMP BINARY

NLS_DUAL_CURRENCY $

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_SORT BINARY

NLS_DATE_LANGUAGE AMERICAN

NLS_DATE_FORMAT DD-MON-RR

NLS_CALENDAR GREGORIAN

NLS_NUMERIC_CHARACTERS .,

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_CHARACTERSET AL32UTF8

NLS_ISO_CURRENCY AMERICA

NLS_CURRENCY $

NLS_TERRITORY AMERICA

NLS_LANGUAGE AMERICAN


When i now create a clob to export like a excel csv list, then the imported Excel Sheet does not show the german characters ö ä ü etc.

How can i export the correct clob data ?

I have a select on db tables and create such a csv file to export:

declare

ls_clob clob;

begin

ls_clob := 'Text;';

for x in (select text_column from table_a) loop

ls_clob := ls_clob || x.text_column || ';' ;

end loop;

end;

Tagged:

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,492 Red Diamond

    Is it WIndows? If so, code page/locale can be part of the issue:

    I:\>chcp 437
    Active code page: 437
    
    I:\>sqlplus [email protected]
    
    SQL*Plus: Release 12.2.0.1.0 Production on Tue May 3 09:33:26 2022
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    Enter password:
    Last Successful login time: Tue May 03 2022 09:30:54 -04:00
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> select 'string is "' || str || '"' from tbl;
    
    'STRINGIS"'||STR||'"'
    ----------------------
    string is "ö ä ü"
    
    SQL>
    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    I:\>chcp 65001
    Active code page: 65001
    
    I:\>sqlplus [email protected]
    
    SQL*Plus: Release 12.2.0.1.0 Production on Tue May 3 09:33:55 2022
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    Enter password:
    Last Successful login time: Tue May 03 2022 09:33:29 -04:00
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> select 'string is "' || str || '"' from tbl;
    
    'STRINGIS"'||STR||'"'
    ----------------------
    string is "  "
    
    SQL>
    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    I:\>
    

    SY.

  • Frank Lehmann
    Frank Lehmann Member Posts: 126 Blue Ribbon

    Hi Solomon,

    thanks for your response, but this is within sqlplus.

    I need a solution within pl/sql procedure.

    I have used the "alter session SET NLS_LANGUAGE=  'GERMAN'  NLS_TERRITORY= ' GERMANY' ;"

    but no change. The characters are still not readable.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,492 Red Diamond

    It is yout OS, not client setting. OS interprets byte codes it receives based on code page:

    I:\>chcp
    Active code page: 65001
    
    I:\>sqlplus [email protected]
    
    SQL*Plus: Release 12.2.0.1.0 Production on Tue May 3 12:14:34 2022
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    Enter password:
    Last Successful login time: Tue May 03 2022 12:14:00 -04:00
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> alter session SET NLS_LANGUAGE=  'GERMAN';
    
    Session wurde gendert.
    
    SQL> alter session SET NLS_TERRITORY= 'GERMANY';
    
    Session wurde gendert.
    
    SQL> select 'string is "' || str || '"' from tbl;
    
    'STRINGIS"'||STR||'"'
    ----------------------
    string is "  "
    
    SQL> exit
    Verbindung zu Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production beendet
    
    I:\>
    

    As you can see it doesn't matter what NLS_LANGUAGE/TERRITORY it is. It is environment variable NLS_LANG or code page in MS DOS you need to setup properly.

    SY.

  • Frank Lehmann
    Frank Lehmann Member Posts: 126 Blue Ribbon

    Hi Solomon,

    how to set NLS_LANG correct ?

    I have set nls_lang=GERMAN_GERMANY.UTF8 and the listener will not start.

  • Frank Lehmann
    Frank Lehmann Member Posts: 126 Blue Ribbon

    Hi Solomon,

    after searching a while i think, i have found the problem.

    The clob i created is fully correct, it has all characters öäü correct.

    But if i download this file as csv to import in MS Excel, then Excel can not convert the UTF-8 file to the correct characters.

    When importing into Excel with using the import mechanism, then i can change the character set to UTF-8 BOM. Then the import is correct with these characters äöü.

    So i have to find a solution to export a clob not only with UTF-8 characterset, but in characterset UTF-8 BOM !

    Is that possible ?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,492 Red Diamond

    I can reproduce the issue. CSV file is ö ä ü. But when I open it in excel I get ö ä ü. I don't know how to teel Excel to use BOM. You need to ask in MS Excel forums.

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,492 Red Diamond

    Figured it out. Open CSV file in word. The following screen will pop up:

    Click OK. Then save. Click Yes for:

    Exit Word. Now Excel will work OK:


    SY.

    KayK