change charset utf8 in oracle

Question

I have problem with transform data in kazakh language from one db oracle to other oracle. Source database has AL32UTF8 NLS_CHARACTERSET (according to v$nls_parameters), target has CL8MSWIN1251 how to solve this? I inserted raw with informatica

Yes, i set BEGIN EXECUTE IMMEDIATE 'alter session set nls_langUAGE = ''RUSSIAN'''; END; — Abylay, Oct 09 '19 at 11:21
You cannot set `NLS_LANG` with any SQL command. `NLS_LANG <> NLS_LANGUAGE`! NLS_LANG can be set only at client level, typically from environment or Registry (in case of Windows) — Wernfried Domscheit, Oct 09 '19 at 11:41
Can I set in session OS? Informatica services running on redhat — Abylay, Oct 09 '19 at 12:04

score 0 · Answer 1 · answered Oct 09 '19 at 05:46

0

According HOW TO: Set the Oracle NLS_LANG environment variable you

Set the NLS_LANG Environment Variable on the machine hosting the Informatica Services to the value which the Oracle Client's OS is using.

For me it is not clear what "the value which the Oracle Client's OS is using" means.

If Informatica runs on Linux/Unix then it might be easy. Check with locale charmap or echo $LANG

On Windows you have actually two character sets. The OEM codepage which applies on console (aka DOS window) and the ANSI codepage which typically applies on GUI applications.

The OEM codepage you can interrogate (and modify) with command chcp

The ANSI codepage you can get from Registry HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP for from National Language Support (NLS) API Reference

I don't know which one applies for Informatica but you should be able to try.

answered Oct 09 '19 at 05:46

Wernfried Domscheit

54,457
9
76
110

informatica on linux redhat. now there en_US.UTF8. if i get correct i should change global settings same in target database host? Is there way to do it in OS session? – Abylay Oct 09 '19 at 12:08
Then set NLS_LANG to `.AL32UTF8` (language and territory can be skipped since all components of NLS_LANG are optional) – Wernfried Domscheit Oct 09 '19 at 12:30
You cannot set NLS_LANG on database - it is a client only setting. See https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600 for more information – Wernfried Domscheit Oct 09 '19 at 12:31

score 0 · Answer 2 · answered Oct 10 '19 at 08:04

The Problem was in code type in target database. Client support utf16.
I decieded to solve it with translate function.

create or replace function fix_kaz_letters(p_word in varchar2) return varchar2 deterministic
  PARALLEL_ENABLE is
  c_bad_letters CONSTANT VARCHAR2(18 CHAR) := CHR(1112 USING NCHAR_CS) /*ә*/
                                              || CHR(1032 USING NCHAR_CS) /*Ә*/
                                              || CHR(1110 USING NCHAR_CS) /*і*/
                                              || CHR(1030 USING NCHAR_CS) /*І*/
                                              || CHR(1169 USING NCHAR_CS) /*ө*/
                                              || CHR(1168 USING NCHAR_CS) /*Ө*/
                                              || CHR(1111 USING NCHAR_CS) /*ү*/
                                              || CHR(1031 USING NCHAR_CS) /*Ү*/
                                              || CHR(1118 USING NCHAR_CS) /*ұ*/
                                              || CHR(1038 USING NCHAR_CS) /*Ұ*/
                                              || CHR(1171 USING NCHAR_CS) /*ғ*/
                                              || CHR(1170 USING NCHAR_CS) /*Ғ*/
                                              || CHR(1179 USING NCHAR_CS) /*қ*/
                                              || CHR(1178 USING NCHAR_CS) /*Қ*/
                                              || CHR(1187 USING NCHAR_CS) /*ң*/
                                              || CHR(1186 USING NCHAR_CS) /*Ң*/
   ;
  c_kaz_letters CONSTANT VARCHAR2(18 NCHAR) := CHR(1241 USING NCHAR_CS) /*ә*/
                                               || CHR(1240 USING NCHAR_CS) /*Ә*/
                                               || CHR(1110 USING NCHAR_CS) /*і*/
                                               || CHR(1030 USING NCHAR_CS) /*І*/
                                               || CHR(1257 USING NCHAR_CS) /*ө*/
                                               || CHR(1256 USING NCHAR_CS) /*Ө*/
                                               || CHR(1199 USING NCHAR_CS) /*ү*/
                                               || CHR(1198 USING NCHAR_CS) /*Ү*/
                                               || CHR(1201 USING NCHAR_CS) /*ұ*/
                                               || CHR(1200 USING NCHAR_CS) /*Ұ*/
                                               || CHR(1108 USING NCHAR_CS) /*ғ*/
                                               || CHR(1028 USING NCHAR_CS) /*Ғ*/
                                               || CHR(1116 USING NCHAR_CS) /*қ*/
                                               || CHR(1036 USING NCHAR_CS) /*Қ*/
                                               || CHR(1109 USING NCHAR_CS) /*ң*/
                                               || CHR(1029 USING NCHAR_CS) /*Ң*/
   ;
begin
  return translate(p_word, c_bad_letters, c_kaz_letters);
end;

change charset utf8 in oracle

2 Answers2