Re: Unexpected conversion from AL32UTF8 to WE8ISO8859P1

From: Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl>
Date: Thu, 8 Jan 2009 06:31:48 -0800 (PST)
Message-ID: <cce8c1dc-7de8-4107-8cb3-c978428d5f5a_at_a26g2000prf.googlegroups.com>



On Jan 8, 12:35 pm, Laurenz Albe <inv..._at_spam.to.invalid> wrote:
> Jaap W. van Dijk <j.w.vand..._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- Hide quoted text -
>
> - Show quoted text -

Hi Laurenz,

Just to be sure about what is in the database: could you execute a 'select dump(val) from nlsdate' on both databases?

Furthermore my, procedure is coded as follows:

CREATE OR REPLACE procedure jvd_conv
as
begin
delete from test_jvd;
dbms_output.put_line('delete: '||sql%rowcount); insert into test_jvd
select * from test_jvd_at_apps_cjibda;
dbms_output.put_line('insert: '||sql%rowcount); commit;
end;
/

Regards,
Jaap. Received on Thu Jan 08 2009 - 08:31:48 CST

Original text of this message