Convert clob data for german settings

Frank Lehmann

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;

Solomon Yakobson

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

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

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

Hi Solomon,

how to set NLS_LANG correct ?

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

Frank Lehmann

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

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

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.