SQL*Plus Copy Command Transforms Certain UTF8 Characters in a UTF8 Database (Doc ID 1105048.1)

Last updated on MAY 31, 2017

Applies to:

SQL*Plus - Version 11.1.0.7 and later
Information in this document applies to any platform.
***Checked for relevance on 30-Nov-2012***

Symptoms

Using SQL*Plus COPY command on UTF8 database, some multibyte characters are converted/transformed to different multibyte characters.

For instance, CHR(52892) (Greek MU) gets copied as CHR(49855).

Example:

CREATE TABLE test_from_52892 as select cast('VXEM6X9YMY3YYY' || chr(52892) || '3Y MOXXERYXEMOM5VXE' as varchar2(255 char)) foo from dual;
CREATE TABLE test_to_52892 as select * from test_from_52892 where rownum < 1;
COPY FROM   scott/tiger@AL32UTF8  TO  scott/tiger@AL32UTF8  INSERT test_to_52892     USING SELECT * FROM test_from_52892
SELECT
a.foo from_string,
b.foo to_string,
substr(a.foo, 15,1) from_char_52892,
substr(b.foo, 15,1) to_char_52892,
ascii(substr(a.foo, 15,1)) ascii_from_char_52892,
ascii(substr(b.foo, 15,1)) ascii_to_char_52892
FROM test_from_52892 a, test_to_52892 b; 
Output
FROM_STRING                                                            TO_STRING                                                             F T  ASCII_FROM_CHAR_49855 ASCII_TO_CHAR_49855
------------------------------------------------------------   -----------------------------------------------------------   - -- --------------                           -------------------------------           
VXEM6X9YMY3YYY�3Y MOXXERYXEMOM5VXE   VXEM6X9YMY3YYY�3Y MOXXERYXEMOM5VXE � �  52892                                   49855                            
There is a conversion from 52892 to 49855.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms