Re: Characters losts during Import (8.1.6) -> (9.2.0.6)

From: Cris Carampa <cris119_at_operamail.com>
Date: Thu, 06 Jul 2006 09:34:19 +0200
Message-ID: <44acbcd7$0$989$5fc30a8@news.tiscali.it>

Sybrand Bakker wrote:

> You are spreading fairy tales.

#
# loads the Oracle environment for a 9.2.0.5.0 US7ASCII database
#

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9ir2
export ORACLE_SID=bol1

export TNS_ADMIN=$ORACLE_HOME/network/admin export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib

#
# sets the NLS_LANG environment value to the same value as the database
# charset
#

$ export NLS_LANG=american_america.US7ASCII

#
# inserts a 8-bit character into a 7-bit database
#

$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.5.0 - Production on Thu Jul 6 09:29:32 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Release 9.2.0.5.0 - Production
JServer Release 9.2.0.5.0 - Production

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE

---

US7ASCII SQL> create table z ( z varchar2(4000) ) ;

Table created.

SQL> insert into z values ('cittą');

1 row created.

#
# dumps the 8-bit character into a text file
#

SQL> spool z.txt

SQL> select z from z ;

Z

---

cittą

SQL> spool off
SQL> exit

#
# shows that the text file is an ISO-8859 file and contains the correct
# character
#

$ file z.txt
z.txt: ISO-8859 text
$ cat z.txt
cittą

#
# loads the Oracle environment for a second database, 10.1.0.4.0 - UTF8
#

export ORACLE_HOME=$ORACLE_BASE/product/10gr1 export ORACLE_SID=bol3
export TNS_ADMIN=$ORACLE_HOME/network/admin unset ORA_NLS33
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib

#
# sets the NLS_LANG environment value according to the text file charset
#

$ export NLS_LANG=american_america.WE8ISO8859P1

#
# creates a z.ctl file
#

load data
infile "z.txt"
append
into table z
(

   z position (1:80)
)

#
# loads z.txt into UTF8 database
#

$ sqlldr scott/tiger z.ctl

SQL*Loader: Release 10.1.0.4.0 - Production on Thu Jul 6 09:27:10 2006

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Commit point reached - logical record count 1

#
# shows the 8-bit character was correctly loaded
#

$ sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Jul 6 09:28:13 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Release 10.1.0.4.0 - Production

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE

---

UTF8 SQL> select z from z ;

Z

---

cittą

-- 
Cris Carampa (spamto:cris119_at_operamail.com)

   blind faith in your leaders or
   in anything will get you killed

Received on Thu Jul 06 2006 - 02:34:19 CDT