Re: Unexpected conversion from AL32UTF8 to WE8ISO8859P1

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 08 Jan 2009 11:35:06 GMT
Message-ID: <1231414504.71703_at_proxy.dienste.wien.at>

Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl> wrote:
> I have two databases:
>
> the source, version 10.2.0.3.0 with AL32UTF8, and
> the target, version 9.2.0.5.0, with WE8ISO8859P1.
>
> I copy a table, containing single and double byte characters, from the
> source database to the target database with "insert into ... select
> from ..." over a database link. The copy is done in a database
> procedure in the target database that is started in a Windows sqlplus
> session.
>
> What happens is that in the target database all the originally double
> byte characters are converted to an inverted question mark, including
> characters that are defined within WE8ISO8859P1.
>
> For instance: a SMALL E ACUTE (code point C265) in the source database
> is converted to an INVERTED QUESTION MARK (code point BF) in the
> target database.
>
> Why is code point C265 not converted to code point E9, which is SMALL
> E ACUTE in WE8ISO8859P1?

Don't know, it works for me between two 10.2.0.4 databases:

I start an sqlplus connection to DB1 (charset AL32UTF8):

CREATE TABLE nlsdata (

   id NUMBER(4) CONSTRAINT nlsdata_pk PRIMARY KEY,    val VARCHAR2(10 CHAR)
);
INSERT INTO nlsdata VALUES (1, 'normal'); INSERT INTO nlsdata VALUES (2, 'été');
COMMIT; Then I connect with sqlplus to DB2 (charset WE8ISO8859P1):

CREATE DATABASE LINK db1

   CONNECT TO db1_user IDENTIFIED BY db1pwd USING 'db1';

CREATE TABLE nlsdata (

   id NUMBER(4) CONSTRAINT nlsdata_pk PRIMARY KEY,    val VARCHAR2(10 CHAR)
);

CREATE PROCEDURE copy_data AUTHID CURRENT_USER AS BEGIN
   INSERT INTO nlsdata SELECT * FROM nlsdata_at_db1; END;
/

Then I connect from a Windows machine with sqlplus and run the procedure:

EXEC copy_data;
COMMIT; Now when I select from table "nlsdata" on db2, everything looks fine:

SELECT * FROM nlsdata;

        ID VAL

---------- ----------------------------------------
         1 normal
         2 été

Maybe you can try to copy my example and see if it works for you.

Can you post your stored procedure and how you call it?

Yours,
Laurenz Albe Received on Thu Jan 08 2009 - 05:35:06 CST