Mixing
Issues with latin chars encoding in Oracle table
I have an oracle database with
NLS_CHARACTERSET=AL32UTF8
sqlplus nls_lang is WE8ISO8859P1
unix shell is iso aswell
There is varchar2 column with letter: LATIN SMALL LETTER O WITH ACUTE
When I call DUMP function on this column I get:
Typ=1 Len=4: 195,131,194,179
This doesn't look like UTF8.
Running SELECT shows corrupted cha on screen.
Running CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8') - shows correct value, but the encoding doesn't look like UTF8...
If I try to insert same letter manually, the select query returns correct char.
The Dump shows 195,179 which is correct UTF8 code.
Running CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8') - fails
ORA-29275: partial multibyte character
Which is strange because 195,179 is indeed UTF8.
Please clarify the above to me.
oracle character-encoding char
asked Nov 20 '18 at 6:55
YevYev
237
|
show 1 more comment
I have an oracle database with
NLS_CHARACTERSET=AL32UTF8
sqlplus nls_lang is WE8ISO8859P1
unix shell is iso aswell
There is varchar2 column with letter: LATIN SMALL LETTER O WITH ACUTE
When I call DUMP function on this column I get:
Typ=1 Len=4: 195,131,194,179
This doesn't look like UTF8.
Running SELECT shows corrupted cha on screen.
Running CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8') - shows correct value, but the encoding doesn't look like UTF8...
If I try to insert same letter manually, the select query returns correct char.
The Dump shows 195,179 which is correct UTF8 code.
Running CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8') - fails
ORA-29275: partial multibyte character
Which is strange because 195,179 is indeed UTF8.
Please clarify the above to me.
oracle character-encoding char
asked Nov 20 '18 at 6:55
YevYev
237
What do you get fromDUMP(..., 1016)?
– Wernfried Domscheit
Nov 20 '18 at 7:24
CONVERT(VALUE, 'UTF8', 'WE8ISO8859P1')is wrong, i.e. this means "Convert fromWE8ISO8859P1toUTF8" - however, your value is already UTF-8 accordingNLS_CHARACTERSET
– Wernfried Domscheit
Nov 20 '18 at 7:37
For value that already existed I get: Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 for new value that I inserted I get: Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3
– Yev
Nov 20 '18 at 7:38
I am sorry , I pasted wrong convert, the original that I used was CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8'), after that I did some test so I switched places... I will update question
– Yev
Nov 20 '18 at 7:39
Which client do you use to insert and query the data?
– Wernfried Domscheit
Nov 20 '18 at 7:46
|
show 1 more comment
I have an oracle database with
NLS_CHARACTERSET=AL32UTF8
sqlplus nls_lang is WE8ISO8859P1
unix shell is iso aswell
There is varchar2 column with letter: LATIN SMALL LETTER O WITH ACUTE
When I call DUMP function on this column I get:
Typ=1 Len=4: 195,131,194,179
This doesn't look like UTF8.
Running SELECT shows corrupted cha on screen.
Running CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8') - shows correct value, but the encoding doesn't look like UTF8...
If I try to insert same letter manually, the select query returns correct char.
The Dump shows 195,179 which is correct UTF8 code.
Running CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8') - fails
ORA-29275: partial multibyte character
Which is strange because 195,179 is indeed UTF8.
Please clarify the above to me.
oracle character-encoding char
asked Nov 20 '18 at 6:55
YevYev
237
I have an oracle database with
NLS_CHARACTERSET=AL32UTF8
sqlplus nls_lang is WE8ISO8859P1
unix shell is iso aswell
There is varchar2 column with letter: LATIN SMALL LETTER O WITH ACUTE
When I call DUMP function on this column I get:
Typ=1 Len=4: 195,131,194,179
This doesn't look like UTF8.
Running SELECT shows corrupted cha on screen.
Running CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8') - shows correct value, but the encoding doesn't look like UTF8...
If I try to insert same letter manually, the select query returns correct char.
The Dump shows 195,179 which is correct UTF8 code.
Running CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8') - fails
ORA-29275: partial multibyte character
Which is strange because 195,179 is indeed UTF8.
Please clarify the above to me.
oracle character-encoding char
oracle character-encoding char
asked Nov 20 '18 at 6:55
YevYev
237
asked Nov 20 '18 at 6:55
YevYev
237
edited Nov 20 '18 at 7:40
Yev
asked Nov 20 '18 at 6:55
YevYev
237
asked Nov 20 '18 at 6:55
YevYev
237
asked Nov 20 '18 at 6:55
YevYev
237
237
What do you get fromDUMP(..., 1016)?
– Wernfried Domscheit
Nov 20 '18 at 7:24
CONVERT(VALUE, 'UTF8', 'WE8ISO8859P1')is wrong, i.e. this means "Convert fromWE8ISO8859P1toUTF8" - however, your value is already UTF-8 accordingNLS_CHARACTERSET
– Wernfried Domscheit
Nov 20 '18 at 7:37
For value that already existed I get: Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 for new value that I inserted I get: Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3
– Yev
Nov 20 '18 at 7:38
I am sorry , I pasted wrong convert, the original that I used was CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8'), after that I did some test so I switched places... I will update question
– Yev
Nov 20 '18 at 7:39
Which client do you use to insert and query the data?
– Wernfried Domscheit
Nov 20 '18 at 7:46
|
show 1 more comment
What do you get fromDUMP(..., 1016)?
– Wernfried Domscheit
Nov 20 '18 at 7:24
CONVERT(VALUE, 'UTF8', 'WE8ISO8859P1')is wrong, i.e. this means "Convert fromWE8ISO8859P1toUTF8" - however, your value is already UTF-8 accordingNLS_CHARACTERSET
– Wernfried Domscheit
Nov 20 '18 at 7:37
For value that already existed I get: Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 for new value that I inserted I get: Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3
– Yev
Nov 20 '18 at 7:38
I am sorry , I pasted wrong convert, the original that I used was CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8'), after that I did some test so I switched places... I will update question
– Yev
Nov 20 '18 at 7:39
Which client do you use to insert and query the data?
– Wernfried Domscheit
Nov 20 '18 at 7:46
What do you get from
DUMP(..., 1016)?– Wernfried Domscheit
Nov 20 '18 at 7:24
What do you get from
DUMP(..., 1016)?– Wernfried Domscheit
Nov 20 '18 at 7:24
CONVERT(VALUE, 'UTF8', 'WE8ISO8859P1') is wrong, i.e. this means "Convert from WE8ISO8859P1 to UTF8" - however, your value is already UTF-8 according NLS_CHARACTERSET– Wernfried Domscheit
Nov 20 '18 at 7:37
CONVERT(VALUE, 'UTF8', 'WE8ISO8859P1') is wrong, i.e. this means "Convert from WE8ISO8859P1 to UTF8" - however, your value is already UTF-8 according NLS_CHARACTERSET– Wernfried Domscheit
Nov 20 '18 at 7:37
For value that already existed I get: Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 for new value that I inserted I get: Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3
– Yev
Nov 20 '18 at 7:38
For value that already existed I get: Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 for new value that I inserted I get: Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3
– Yev
Nov 20 '18 at 7:38
I am sorry , I pasted wrong convert, the original that I used was CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8'), after that I did some test so I switched places... I will update question
– Yev
Nov 20 '18 at 7:39
I am sorry , I pasted wrong convert, the original that I used was CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8'), after that I did some test so I switched places... I will update question
– Yev
Nov 20 '18 at 7:39
Which client do you use to insert and query the data?
– Wernfried Domscheit
Nov 20 '18 at 7:46
Which client do you use to insert and query the data?
– Wernfried Domscheit
Nov 20 '18 at 7:46
|
show 1 more comment
1 Answer
1
active
oldest
votes
Value Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 was obviously inserted wrongly.
It happened when the client sent the characters as UTF-8 but the database treated this data as WE8ISO8859P1.
If the client sends data as UTF-8 then you must also set NLS_LANG=.AL32UTF8 (language and territory are optional and not relevant here).
However when you run sqlplus then sqlplus inherits character set from command line which seems to be ISO 8859-1. So before you start sqlplus you must run export NLS_LANG=.WE8ISO8859P1 in order to set it properly.
I create a table with your data:
SELECT
val,
DUMP(val, 1016) as dump_val,
DUMP(CONVERT(VAL, 'WE8ISO8859P1'), 1016) as conv_dump
FROM AA;
+-------------------------------------------------------------------------------------------+
|VAL|DUMP_VAL |CONV_DUMP |
+-------------------------------------------------------------------------------------------+
|ó |Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3|Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3|
|ó |Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3 |Typ=1 Len=1 CharacterSet=AL32UTF8: f3 |
+-------------------------------------------------------------------------------------------+
Row 1 shows corrupt data, row 2 is fine.
CONVERT(VAL, 'WE8ISO8859P1') is used to convert corrupt data to correct character set, so conversion of CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C383C2B3'), 'WE8ISO8859P1') is fine.
However CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C3B3'), 'WE8ISO8859P1') would return hex F3 which is not a valid UTF-8 byte sequence, thus you get an ORA-29275.
answered Nov 20 '18 at 7:46
Wernfried DomscheitWernfried Domscheit
24k42858
Still not clear, ' database treated this data as WE8ISO8859P1' but NLS_CHARACTERSET=AL32UTF8. c3,83,c2,b3 'is obviously wrong', but why covert worked for this and didn't work for 195,179 which is true UTF8?
– Yev
Nov 20 '18 at 8:00
NLS_LANG is already iso...
– Yev
Nov 20 '18 at 8:13
Yes, but when "for value that already existed" was inserted (perhaps by somebody else) then NLS_LANG was set wrong.
– Wernfried Domscheit
Nov 20 '18 at 8:40
Yes this is true! it was inserted with some other NLS_LANG (don't know which). Why the data that I inserted manually, and looks like UTF8 encoding 195,179 , why convert function is not working, and why it did work for 195,131,194,179?
– Yev
Nov 20 '18 at 8:54
1
Convert is mainly used to correct corrupt data (that's the only reason why one should use it). However 'C3B3' is not corrupt, there is nothing to correct.
– Wernfried Domscheit
Nov 20 '18 at 10:06
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53387724%2fissues-with-latin-chars-encoding-in-oracle-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Value Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 was obviously inserted wrongly.
It happened when the client sent the characters as UTF-8 but the database treated this data as WE8ISO8859P1.
If the client sends data as UTF-8 then you must also set NLS_LANG=.AL32UTF8 (language and territory are optional and not relevant here).
However when you run sqlplus then sqlplus inherits character set from command line which seems to be ISO 8859-1. So before you start sqlplus you must run export NLS_LANG=.WE8ISO8859P1 in order to set it properly.
I create a table with your data:
SELECT
val,
DUMP(val, 1016) as dump_val,
DUMP(CONVERT(VAL, 'WE8ISO8859P1'), 1016) as conv_dump
FROM AA;
+-------------------------------------------------------------------------------------------+
|VAL|DUMP_VAL |CONV_DUMP |
+-------------------------------------------------------------------------------------------+
|ó |Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3|Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3|
|ó |Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3 |Typ=1 Len=1 CharacterSet=AL32UTF8: f3 |
+-------------------------------------------------------------------------------------------+
Row 1 shows corrupt data, row 2 is fine.
CONVERT(VAL, 'WE8ISO8859P1') is used to convert corrupt data to correct character set, so conversion of CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C383C2B3'), 'WE8ISO8859P1') is fine.
However CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C3B3'), 'WE8ISO8859P1') would return hex F3 which is not a valid UTF-8 byte sequence, thus you get an ORA-29275.
answered Nov 20 '18 at 7:46
Wernfried DomscheitWernfried Domscheit
24k42858
Still not clear, ' database treated this data as WE8ISO8859P1' but NLS_CHARACTERSET=AL32UTF8. c3,83,c2,b3 'is obviously wrong', but why covert worked for this and didn't work for 195,179 which is true UTF8?
– Yev
Nov 20 '18 at 8:00
NLS_LANG is already iso...
– Yev
Nov 20 '18 at 8:13
Yes, but when "for value that already existed" was inserted (perhaps by somebody else) then NLS_LANG was set wrong.
– Wernfried Domscheit
Nov 20 '18 at 8:40
Yes this is true! it was inserted with some other NLS_LANG (don't know which). Why the data that I inserted manually, and looks like UTF8 encoding 195,179 , why convert function is not working, and why it did work for 195,131,194,179?
– Yev
Nov 20 '18 at 8:54
1
Convert is mainly used to correct corrupt data (that's the only reason why one should use it). However 'C3B3' is not corrupt, there is nothing to correct.
– Wernfried Domscheit
Nov 20 '18 at 10:06
|
show 2 more comments
Value Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 was obviously inserted wrongly.
It happened when the client sent the characters as UTF-8 but the database treated this data as WE8ISO8859P1.
If the client sends data as UTF-8 then you must also set NLS_LANG=.AL32UTF8 (language and territory are optional and not relevant here).
However when you run sqlplus then sqlplus inherits character set from command line which seems to be ISO 8859-1. So before you start sqlplus you must run export NLS_LANG=.WE8ISO8859P1 in order to set it properly.
I create a table with your data:
SELECT
val,
DUMP(val, 1016) as dump_val,
DUMP(CONVERT(VAL, 'WE8ISO8859P1'), 1016) as conv_dump
FROM AA;
+-------------------------------------------------------------------------------------------+
|VAL|DUMP_VAL |CONV_DUMP |
+-------------------------------------------------------------------------------------------+
|ó |Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3|Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3|
|ó |Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3 |Typ=1 Len=1 CharacterSet=AL32UTF8: f3 |
+-------------------------------------------------------------------------------------------+
Row 1 shows corrupt data, row 2 is fine.
CONVERT(VAL, 'WE8ISO8859P1') is used to convert corrupt data to correct character set, so conversion of CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C383C2B3'), 'WE8ISO8859P1') is fine.
However CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C3B3'), 'WE8ISO8859P1') would return hex F3 which is not a valid UTF-8 byte sequence, thus you get an ORA-29275.
answered Nov 20 '18 at 7:46
Wernfried DomscheitWernfried Domscheit
24k42858
Still not clear, ' database treated this data as WE8ISO8859P1' but NLS_CHARACTERSET=AL32UTF8. c3,83,c2,b3 'is obviously wrong', but why covert worked for this and didn't work for 195,179 which is true UTF8?
– Yev
Nov 20 '18 at 8:00
NLS_LANG is already iso...
– Yev
Nov 20 '18 at 8:13
Yes, but when "for value that already existed" was inserted (perhaps by somebody else) then NLS_LANG was set wrong.
– Wernfried Domscheit
Nov 20 '18 at 8:40
Yes this is true! it was inserted with some other NLS_LANG (don't know which). Why the data that I inserted manually, and looks like UTF8 encoding 195,179 , why convert function is not working, and why it did work for 195,131,194,179?
– Yev
Nov 20 '18 at 8:54
1
Convert is mainly used to correct corrupt data (that's the only reason why one should use it). However 'C3B3' is not corrupt, there is nothing to correct.
– Wernfried Domscheit
Nov 20 '18 at 10:06
|
show 2 more comments
Value Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 was obviously inserted wrongly.
It happened when the client sent the characters as UTF-8 but the database treated this data as WE8ISO8859P1.
If the client sends data as UTF-8 then you must also set NLS_LANG=.AL32UTF8 (language and territory are optional and not relevant here).
However when you run sqlplus then sqlplus inherits character set from command line which seems to be ISO 8859-1. So before you start sqlplus you must run export NLS_LANG=.WE8ISO8859P1 in order to set it properly.
I create a table with your data:
SELECT
val,
DUMP(val, 1016) as dump_val,
DUMP(CONVERT(VAL, 'WE8ISO8859P1'), 1016) as conv_dump
FROM AA;
+-------------------------------------------------------------------------------------------+
|VAL|DUMP_VAL |CONV_DUMP |
+-------------------------------------------------------------------------------------------+
|ó |Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3|Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3|
|ó |Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3 |Typ=1 Len=1 CharacterSet=AL32UTF8: f3 |
+-------------------------------------------------------------------------------------------+
Row 1 shows corrupt data, row 2 is fine.
CONVERT(VAL, 'WE8ISO8859P1') is used to convert corrupt data to correct character set, so conversion of CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C383C2B3'), 'WE8ISO8859P1') is fine.
However CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C3B3'), 'WE8ISO8859P1') would return hex F3 which is not a valid UTF-8 byte sequence, thus you get an ORA-29275.
answered Nov 20 '18 at 7:46
Wernfried DomscheitWernfried Domscheit
24k42858
Value Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 was obviously inserted wrongly.
It happened when the client sent the characters as UTF-8 but the database treated this data as WE8ISO8859P1.
If the client sends data as UTF-8 then you must also set NLS_LANG=.AL32UTF8 (language and territory are optional and not relevant here).
However when you run sqlplus then sqlplus inherits character set from command line which seems to be ISO 8859-1. So before you start sqlplus you must run export NLS_LANG=.WE8ISO8859P1 in order to set it properly.
I create a table with your data:
SELECT
val,
DUMP(val, 1016) as dump_val,
DUMP(CONVERT(VAL, 'WE8ISO8859P1'), 1016) as conv_dump
FROM AA;
+-------------------------------------------------------------------------------------------+
|VAL|DUMP_VAL |CONV_DUMP |
+-------------------------------------------------------------------------------------------+
|ó |Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3|Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3|
|ó |Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3 |Typ=1 Len=1 CharacterSet=AL32UTF8: f3 |
+-------------------------------------------------------------------------------------------+
Row 1 shows corrupt data, row 2 is fine.
CONVERT(VAL, 'WE8ISO8859P1') is used to convert corrupt data to correct character set, so conversion of CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C383C2B3'), 'WE8ISO8859P1') is fine.
However CONVERT(UTL_RAW.CAST_TO_VARCHAR2('C3B3'), 'WE8ISO8859P1') would return hex F3 which is not a valid UTF-8 byte sequence, thus you get an ORA-29275.
answered Nov 20 '18 at 7:46
Wernfried DomscheitWernfried Domscheit
24k42858
edited Nov 20 '18 at 9:50
answered Nov 20 '18 at 7:46
Wernfried DomscheitWernfried Domscheit
24k42858
answered Nov 20 '18 at 7:46
Wernfried DomscheitWernfried Domscheit
24k42858
answered Nov 20 '18 at 7:46
Wernfried DomscheitWernfried Domscheit
24k42858
24k42858
Still not clear, ' database treated this data as WE8ISO8859P1' but NLS_CHARACTERSET=AL32UTF8. c3,83,c2,b3 'is obviously wrong', but why covert worked for this and didn't work for 195,179 which is true UTF8?
– Yev
Nov 20 '18 at 8:00
NLS_LANG is already iso...
– Yev
Nov 20 '18 at 8:13
Yes, but when "for value that already existed" was inserted (perhaps by somebody else) then NLS_LANG was set wrong.
– Wernfried Domscheit
Nov 20 '18 at 8:40
Yes this is true! it was inserted with some other NLS_LANG (don't know which). Why the data that I inserted manually, and looks like UTF8 encoding 195,179 , why convert function is not working, and why it did work for 195,131,194,179?
– Yev
Nov 20 '18 at 8:54
1
Convert is mainly used to correct corrupt data (that's the only reason why one should use it). However 'C3B3' is not corrupt, there is nothing to correct.
– Wernfried Domscheit
Nov 20 '18 at 10:06
|
show 2 more comments
Still not clear, ' database treated this data as WE8ISO8859P1' but NLS_CHARACTERSET=AL32UTF8. c3,83,c2,b3 'is obviously wrong', but why covert worked for this and didn't work for 195,179 which is true UTF8?
– Yev
Nov 20 '18 at 8:00
NLS_LANG is already iso...
– Yev
Nov 20 '18 at 8:13
Yes, but when "for value that already existed" was inserted (perhaps by somebody else) then NLS_LANG was set wrong.
– Wernfried Domscheit
Nov 20 '18 at 8:40
Yes this is true! it was inserted with some other NLS_LANG (don't know which). Why the data that I inserted manually, and looks like UTF8 encoding 195,179 , why convert function is not working, and why it did work for 195,131,194,179?
– Yev
Nov 20 '18 at 8:54
1
Convert is mainly used to correct corrupt data (that's the only reason why one should use it). However 'C3B3' is not corrupt, there is nothing to correct.
– Wernfried Domscheit
Nov 20 '18 at 10:06
Still not clear, ' database treated this data as WE8ISO8859P1' but NLS_CHARACTERSET=AL32UTF8. c3,83,c2,b3 'is obviously wrong', but why covert worked for this and didn't work for 195,179 which is true UTF8?
– Yev
Nov 20 '18 at 8:00
Still not clear, ' database treated this data as WE8ISO8859P1' but NLS_CHARACTERSET=AL32UTF8. c3,83,c2,b3 'is obviously wrong', but why covert worked for this and didn't work for 195,179 which is true UTF8?
– Yev
Nov 20 '18 at 8:00
NLS_LANG is already iso...
– Yev
Nov 20 '18 at 8:13
NLS_LANG is already iso...
– Yev
Nov 20 '18 at 8:13
Yes, but when "for value that already existed" was inserted (perhaps by somebody else) then NLS_LANG was set wrong.
– Wernfried Domscheit
Nov 20 '18 at 8:40
Yes, but when "for value that already existed" was inserted (perhaps by somebody else) then NLS_LANG was set wrong.
– Wernfried Domscheit
Nov 20 '18 at 8:40
Yes this is true! it was inserted with some other NLS_LANG (don't know which). Why the data that I inserted manually, and looks like UTF8 encoding 195,179 , why convert function is not working, and why it did work for 195,131,194,179?
– Yev
Nov 20 '18 at 8:54
Yes this is true! it was inserted with some other NLS_LANG (don't know which). Why the data that I inserted manually, and looks like UTF8 encoding 195,179 , why convert function is not working, and why it did work for 195,131,194,179?
– Yev
Nov 20 '18 at 8:54
1
1
Convert is mainly used to correct corrupt data (that's the only reason why one should use it). However 'C3B3' is not corrupt, there is nothing to correct.
– Wernfried Domscheit
Nov 20 '18 at 10:06
Convert is mainly used to correct corrupt data (that's the only reason why one should use it). However 'C3B3' is not corrupt, there is nothing to correct.
– Wernfried Domscheit
Nov 20 '18 at 10:06
|
show 2 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53387724%2fissues-with-latin-chars-encoding-in-oracle-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What do you get from
DUMP(..., 1016)?– Wernfried Domscheit
Nov 20 '18 at 7:24
CONVERT(VALUE, 'UTF8', 'WE8ISO8859P1')is wrong, i.e. this means "Convert fromWE8ISO8859P1toUTF8" - however, your value is already UTF-8 accordingNLS_CHARACTERSET– Wernfried Domscheit
Nov 20 '18 at 7:37
For value that already existed I get: Typ=1 Len=4 CharacterSet=AL32UTF8: c3,83,c2,b3 for new value that I inserted I get: Typ=1 Len=2 CharacterSet=AL32UTF8: c3,b3
– Yev
Nov 20 '18 at 7:38
I am sorry , I pasted wrong convert, the original that I used was CONVERT(VALUE, 'WE8ISO8859P1', 'UTF8'), after that I did some test so I switched places... I will update question
– Yev
Nov 20 '18 at 7:39
Which client do you use to insert and query the data?
– Wernfried Domscheit
Nov 20 '18 at 7:46