RE: Change Character sets
Developers who know their stuff but don't know Oracle. I don't know Java.
US7ASCII works. Other character sets don't. Why? Just one of life's
little mysteries as far as I'm concerned.
Probably there's something they could do to make it work with other
character sets but I don't know what it is and neither do they.
"Jesse, Rich"
@qtiworld.com> cc:
Sent by: Subject: RE: Change Character sets
ml-errors
10/22/2003 07:04
PM
Please respond
to ORACLE-L
How/where did you find this? I know older versions of Perl/DBI/DBD::Oracle
silently fail on login attempts, but we've had no problems with Java on
8.1.7.4/HPUX.
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
> -Original Message-
> From: Thomas Day [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 22, 2003 2:45 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Change Character sets
>
>
>
>
> Java drivers seem to require US7ASCII.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Change Character sets
Yup. That's the ticket.
"Quintin,
Richard" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: Change Character sets
Sent by:
ml-errors
10/22/2003 03:49
PM
Please respond
to ORACLE-L
You might try ALTER DATABASE CHARACTER SET internal_use us7ascii;
Make sure you know what you're doing. See Metalink Doc Id 100751.996
On Wed, 2003-10-22 at 15:29, Goulet, Dick wrote:
> Thomas,
>
>Well I can't help you on that score. I do remember a
discussion on going from US7ACSII to WE8ISO8859P1. And if my memory
servers me correctly that is a one way trip. I believe you may well have
to re-export the data & rebuild the database. Is there a specific reason
why you need to do that? Personally I'd leave well enough alone.
>
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
>
> -Original Message-
> Sent: Wednesday, October 22, 2003 3:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> But I want to do it the other way round. No, there are no '?'s. The
data
> is all US7ASCII. Yes, you're right, the import was done into a database
> with the wrong character set. I know about the export/import route but I
> believe that there is some utility that will do the conversion from
> WE8ISO8859P1 to US7ASCII as long as all the data in the database is
> compatible with US7ASCII (as is the case). I just don't remember what it
> is or where to find it. I remember seeing a discussion of this (I
believe
> it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle
> documentation has not turned up anything. Maybe it's just wishful
thinking
> but I'm hoping that someone would have a better memory and could point me
> in the right direction.
>
> SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
>
> PARAMETER VALUE
> --
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA
> NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_CHARACTERSET WE8ISO8859P1
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMATDD-MON-RR
> NLS_DATE_LANGUAGE AMERICAN
> NLS_SORT BINARY
> NLS_TIME_FORMATHH.MI.SSXFF AM
> NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
> NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
> NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
> NLS_DUAL_CURRENCY $
> NLS_COMP BINARY
> NLS_LENGTH_SEMANTICS BYTE
> NLS_NCHAR_CONV_EXCPFALSE
> NLS_NCHAR_CHARACTERSET AL16UTF16
> NLS_RDBMS_VERSION 9.2.0.3.0
>
> SQL> ALTER DATABASE CHARACTER SET US7ASCII;
> ALTER DATABASE CHARACTER SET US7ASCII
> *
> ERROR at line 1:
> ORA-12712: new character set must be a superset of old character set
>
>
>
>
>
> "Goulet, Dick"
RE: Change Character sets
Thanks. But since it was an import from an US7ASCII database I am completely confident that there'll be no problems. But that's about the only circumstances in which I'd try something like that. "Goulet, Dick" @vicr.com> cc: Sent by: Subject: RE: Change Character sets ml-errors 10/22/2003 05:24 PM Please respond to ORACLE-L Well, let me know when the shotgun goes off. I'll send the black lab out to find you!! *-) Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, October 22, 2003 4:20 PM To: Multiple recipients of list ORACLE-L I found it. Trying to explain it tripped those last few synapses. Not sure if it should be shared since it can completely destroy your database. But when you're certain about your data, it's a lifesaver. Kids, don't try this at home - Alter database character set INTERNAL_USE new_character_set No restrictions on the new_character_set but if you're wrong about your data you're one dead duck. "Goulet, Dick" @vicr.com> cc: Sent by: Subject: RE: Change Character sets ml-errors 10/22/2003 03:29 PM Please respond to ORACLE-L Thomas, Well I can't help you on that score. I do remember a discussion on going from US7ACSII to WE8ISO8859P1. And if my memory servers me correctly that is a one way trip. I believe you may well have to re-export the data & rebuild the database. Is there a specific reason why you need to do that? Personally I'd leave well enough alone. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, October 22, 2003 3:09 PM To: Multiple recipients of list ORACLE-L But I want to do it the other way round. No, there are no '?'s. The data is all US7ASCII. Yes, you're right, the import was done into a database with the wrong character set. I know about the export/import route but I believe that there is some utility that will do the conversion from WE8ISO8859P1 to US7ASCII as long as all the data in the database is compatible with US7ASCII (as is the case). I just don't remember what it is or where to find it. I remember seeing a discussion of this (I believe it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle documentation has not turned up anything. Maybe it's just wishful thinking but I'm hoping that someone would have a better memory and could point me in the right direction. SQL> SELECT * FROM NLS_DATABASE_PARAMETERS; PARAMETER VALUE -- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8ISO8859P1 NLS_CALENDAR GREGORIAN NLS_DATE_FORMATDD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT
RE: Change Character sets
How/where did you find this? I know older versions of Perl/DBI/DBD::Oracle silently fail on login attempts, but we've had no problems with Java on 8.1.7.4/HPUX. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > -Original Message- > From: Thomas Day [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 22, 2003 2:45 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Change Character sets > > > > > Java drivers seem to require US7ASCII. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Change Character sets
Java drivers seem to require US7ASCII. I'd love to leave it alone. "Goulet, Dick" @vicr.com> cc: Sent by: Subject: RE: Change Character sets ml-errors 10/22/2003 03:29 PM Please respond to ORACLE-L Thomas, Well I can't help you on that score. I do remember a discussion on going from US7ACSII to WE8ISO8859P1. And if my memory servers me correctly that is a one way trip. I believe you may well have to re-export the data & rebuild the database. Is there a specific reason why you need to do that? Personally I'd leave well enough alone. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, October 22, 2003 3:09 PM To: Multiple recipients of list ORACLE-L But I want to do it the other way round. No, there are no '?'s. The data is all US7ASCII. Yes, you're right, the import was done into a database with the wrong character set. I know about the export/import route but I believe that there is some utility that will do the conversion from WE8ISO8859P1 to US7ASCII as long as all the data in the database is compatible with US7ASCII (as is the case). I just don't remember what it is or where to find it. I remember seeing a discussion of this (I believe it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle documentation has not turned up anything. Maybe it's just wishful thinking but I'm hoping that someone would have a better memory and could point me in the right direction. SQL> SELECT * FROM NLS_DATABASE_PARAMETERS; PARAMETER VALUE -- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8ISO8859P1 NLS_CALENDAR GREGORIAN NLS_DATE_FORMATDD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMATHH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCPFALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 9.2.0.3.0 SQL> ALTER DATABASE CHARACTER SET US7ASCII; ALTER DATABASE CHARACTER SET US7ASCII * ERROR at line 1: ORA-12712: new character set must be a superset of old character set "Goulet, Dick" @vicr.com> cc: Sent by: Subject: RE: Change Character sets ml-errors 10/22/2003 02:09 PM Please respond to ORACLE-L Sounds to me like someone exported their old database into a new one and now have characters showing up as '?' when there was something meaningful before. BTDT. The problem is that yo
RE: Change Character sets
But I want to do it the other way round. No, there are no '?'s. The data
is all US7ASCII. Yes, you're right, the import was done into a database
with the wrong character set. I know about the export/import route but I
believe that there is some utility that will do the conversion from
WE8ISO8859P1 to US7ASCII as long as all the data in the database is
compatible with US7ASCII (as is the case). I just don't remember what it
is or where to find it. I remember seeing a discussion of this (I believe
it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle
documentation has not turned up anything. Maybe it's just wishful thinking
but I'm hoping that someone would have a better memory and could point me
in the right direction.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
--
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMATHH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCPFALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.3.0
SQL> ALTER DATABASE CHARACTER SET US7ASCII;
ALTER DATABASE CHARACTER SET US7ASCII
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
"Goulet, Dick"
@vicr.com> cc:
Sent by: Subject: RE: Change Character sets
ml-errors
10/22/2003 02:09
PM
Please respond
to ORACLE-L
Sounds to me like someone exported their old database into a new one and
now have characters showing up as '?' when there was something meaningful
before. BTDT. The problem is that your NCHAR/NLS_LANG setting did not
match the database character set. This can happen at two points in the
process. Here is what happen to me, see if it matches your case:
Original database created with US7ASCII and then changed to
WE8ISO8859P1.
Client workstation was set up as WE8ISO8859P1 from WAY back,
prior to database character set change.
Export was taken using NLS_LANG=American_america.US7ASCII.
Import doen using NLS_LANG=American_america.WE8ISO8859P1
Lots of data started showing up with '?''s in the middle of
words, etc...
Solution:
Take a new export of the affected tables from the original
database using the right NLS_LANG setting & use that to replace them in the
new database.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Wednesday, October 22, 2003 1:44 PM
To: Multiple recipients of list ORACLE-L
Actually, WE8ISO8859P1 *is* a superset of US7ASC
RE: Change Character sets
You might try ALTER DATABASE CHARACTER SET internal_use us7ascii; Make sure you know what you're doing. See Metalink Doc Id 100751.996 On Wed, 2003-10-22 at 15:29, Goulet, Dick wrote: > Thomas, > > Well I can't help you on that score. I do remember a discussion on going from > US7ACSII to WE8ISO8859P1. And if my memory servers me correctly that is a one way > trip. I believe you may well have to re-export the data & rebuild the database. Is > there a specific reason why you need to do that? Personally I'd leave well enough > alone. > > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA > > -Original Message- > Sent: Wednesday, October 22, 2003 3:09 PM > To: Multiple recipients of list ORACLE-L > > > > But I want to do it the other way round. No, there are no '?'s. The data > is all US7ASCII. Yes, you're right, the import was done into a database > with the wrong character set. I know about the export/import route but I > believe that there is some utility that will do the conversion from > WE8ISO8859P1 to US7ASCII as long as all the data in the database is > compatible with US7ASCII (as is the case). I just don't remember what it > is or where to find it. I remember seeing a discussion of this (I believe > it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle > documentation has not turned up anything. Maybe it's just wishful thinking > but I'm hoping that someone would have a better memory and could point me > in the right direction. > > SQL> SELECT * FROM NLS_DATABASE_PARAMETERS; > > PARAMETER VALUE > -- > NLS_LANGUAGE AMERICAN > NLS_TERRITORY AMERICA > NLS_CURRENCY $ > NLS_ISO_CURRENCY AMERICA > NLS_NUMERIC_CHARACTERS ., > NLS_CHARACTERSET WE8ISO8859P1 > NLS_CALENDAR GREGORIAN > NLS_DATE_FORMATDD-MON-RR > NLS_DATE_LANGUAGE AMERICAN > NLS_SORT BINARY > NLS_TIME_FORMATHH.MI.SSXFF AM > NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM > NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR > NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR > NLS_DUAL_CURRENCY $ > NLS_COMP BINARY > NLS_LENGTH_SEMANTICS BYTE > NLS_NCHAR_CONV_EXCPFALSE > NLS_NCHAR_CHARACTERSET AL16UTF16 > NLS_RDBMS_VERSION 9.2.0.3.0 > > SQL> ALTER DATABASE CHARACTER SET US7ASCII; > ALTER DATABASE CHARACTER SET US7ASCII > * > ERROR at line 1: > ORA-12712: new character set must be a superset of old character set > > > > > > > "Goulet, Dick" > > ORACLE-L <[EMAIL PROTECTED]> > @vicr.com> cc: > > Sent by: Subject: RE: Change Character sets > > ml-errors > > > > > > 10/22/2003 02:09 > > PM > > Please respond > > to ORACLE-L > > > > >
RE: Change Character sets
Well, let me know when the shotgun goes off. I'll send the black lab out to find
you!! *-)
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Wednesday, October 22, 2003 4:20 PM
To: Multiple recipients of list ORACLE-L
I found it. Trying to explain it tripped those last few synapses. Not
sure if it should be shared since it can completely destroy your database.
But when you're certain about your data, it's a lifesaver.
Kids, don't try this at home -
Alter database character set INTERNAL_USE new_character_set
No restrictions on the new_character_set but if you're wrong about your
data you're one dead duck.
"Goulet, Dick"
@vicr.com> cc:
Sent by: Subject: RE: Change Character sets
ml-errors
10/22/2003 03:29
PM
Please respond
to ORACLE-L
Thomas,
Well I can't help you on that score. I do remember a
discussion on going from US7ACSII to WE8ISO8859P1. And if my memory
servers me correctly that is a one way trip. I believe you may well have
to re-export the data & rebuild the database. Is there a specific reason
why you need to do that? Personally I'd leave well enough alone.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Wednesday, October 22, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L
But I want to do it the other way round. No, there are no '?'s. The data
is all US7ASCII. Yes, you're right, the import was done into a database
with the wrong character set. I know about the export/import route but I
believe that there is some utility that will do the conversion from
WE8ISO8859P1 to US7ASCII as long as all the data in the database is
compatible with US7ASCII (as is the case). I just don't remember what it
is or where to find it. I remember seeing a discussion of this (I believe
it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle
documentation has not turned up anything. Maybe it's just wishful thinking
but I'm hoping that someone would have a better memory and could point me
in the right direction.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
--
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMATHH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCPFALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.3.0
SQL> ALTER DATABASE CHARACTER SET US7ASCII;
ALTER DATABASE CHARACTER SET US7ASCII
*
ERROR at line 1:
ORA-12712: new c
RE: Change Character sets
I found it. Trying to explain it tripped those last few synapses. Not
sure if it should be shared since it can completely destroy your database.
But when you're certain about your data, it's a lifesaver.
Kids, don't try this at home -
Alter database character set INTERNAL_USE new_character_set
No restrictions on the new_character_set but if you're wrong about your
data you're one dead duck.
"Goulet, Dick"
@vicr.com> cc:
Sent by: Subject: RE: Change Character sets
ml-errors
10/22/2003 03:29
PM
Please respond
to ORACLE-L
Thomas,
Well I can't help you on that score. I do remember a
discussion on going from US7ACSII to WE8ISO8859P1. And if my memory
servers me correctly that is a one way trip. I believe you may well have
to re-export the data & rebuild the database. Is there a specific reason
why you need to do that? Personally I'd leave well enough alone.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Wednesday, October 22, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L
But I want to do it the other way round. No, there are no '?'s. The data
is all US7ASCII. Yes, you're right, the import was done into a database
with the wrong character set. I know about the export/import route but I
believe that there is some utility that will do the conversion from
WE8ISO8859P1 to US7ASCII as long as all the data in the database is
compatible with US7ASCII (as is the case). I just don't remember what it
is or where to find it. I remember seeing a discussion of this (I believe
it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle
documentation has not turned up anything. Maybe it's just wishful thinking
but I'm hoping that someone would have a better memory and could point me
in the right direction.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
--
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMATHH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCPFALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.3.0
SQL> ALTER DATABASE CHARACTER SET US7ASCII;
ALTER DATABASE CHARACTER SET US7ASCII
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
"Goulet, Dick"
@vicr.com> cc:
Sent by: Subject: RE: Change
Character sets
ml-err
RE: Change Character sets
Thomas,
Well I can't help you on that score. I do remember a discussion on going from
US7ACSII to WE8ISO8859P1. And if my memory servers me correctly that is a one way
trip. I believe you may well have to re-export the data & rebuild the database. Is
there a specific reason why you need to do that? Personally I'd leave well enough
alone.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Wednesday, October 22, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L
But I want to do it the other way round. No, there are no '?'s. The data
is all US7ASCII. Yes, you're right, the import was done into a database
with the wrong character set. I know about the export/import route but I
believe that there is some utility that will do the conversion from
WE8ISO8859P1 to US7ASCII as long as all the data in the database is
compatible with US7ASCII (as is the case). I just don't remember what it
is or where to find it. I remember seeing a discussion of this (I believe
it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle
documentation has not turned up anything. Maybe it's just wishful thinking
but I'm hoping that someone would have a better memory and could point me
in the right direction.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
--
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMATHH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCPFALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.3.0
SQL> ALTER DATABASE CHARACTER SET US7ASCII;
ALTER DATABASE CHARACTER SET US7ASCII
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
"Goulet, Dick"
@vicr.com> cc:
Sent by: Subject: RE: Change Character sets
ml-errors
10/22/2003 02:09
PM
Please respond
to ORACLE-L
Sounds to me like someone exported their old database into a new one and
now have characters showing up as '?' when there was something meaningful
before. BTDT. The problem is that your NCHAR/NLS_LANG setting did not
match the database character set. This can happen at two points in the
process. Here is what happen to me, see if it matches your case:
Original database created with US7ASCII and then changed to
WE8ISO8859P1.
Client workstation was set up as WE8ISO8859P1 from WAY back,
prior to database character set change.
Export was taken using NLS_LANG=American_america.US7ASCII.
Import do
RE: Change Character sets
Actually, WE8ISO8859P1 *is* a superset of US7ASCII, which is how we were able to do the same ALTER DATABASE (in 8.1.7.2 at the time) you mentioned. Check out http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=11 9164.1&p_database_id=NOT (pasting the link together) for a list of valid supersets. Your combo is sixth in the list. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > -Original Message- > From: Thomas Day [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 22, 2003 12:34 PM > To: Multiple recipients of list ORACLE-L > Subject: Change Character sets > > > > I have a database that was populated using an export from an US7ASCII > Oracle database. The current database is WE8ISO8859P1 which is not a > superset of US7ASCII. So I can't change the characterset > using the ALTER > DATABASE as the manual suggests. The alternative is to export the > database, drop the current instance, create a new instance > with the correct > characterset and import the old database. > > But I know that there is no data in the database that was not > supported in > the US7ASCII database. Is there a shortcut? The UPDATE > sys.PROPS$ trick > is a no-go. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Change Character sets
Sounds to me like someone exported their old database into a new one and now have characters showing up as '?' when there was something meaningful before. BTDT. The problem is that your NCHAR/NLS_LANG setting did not match the database character set. This can happen at two points in the process. Here is what happen to me, see if it matches your case: Original database created with US7ASCII and then changed to WE8ISO8859P1. Client workstation was set up as WE8ISO8859P1 from WAY back, prior to database character set change. Export was taken using NLS_LANG=American_america.US7ASCII. Import doen using NLS_LANG=American_america.WE8ISO8859P1 Lots of data started showing up with '?''s in the middle of words, etc... Solution: Take a new export of the affected tables from the original database using the right NLS_LANG setting & use that to replace them in the new database. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, October 22, 2003 1:44 PM To: Multiple recipients of list ORACLE-L Actually, WE8ISO8859P1 *is* a superset of US7ASCII, which is how we were able to do the same ALTER DATABASE (in 8.1.7.2 at the time) you mentioned. Check out http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=11 9164.1&p_database_id=NOT (pasting the link together) for a list of valid supersets. Your combo is sixth in the list. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > -Original Message- > From: Thomas Day [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 22, 2003 12:34 PM > To: Multiple recipients of list ORACLE-L > Subject: Change Character sets > > > > I have a database that was populated using an export from an US7ASCII > Oracle database. The current database is WE8ISO8859P1 which is not a > superset of US7ASCII. So I can't change the characterset > using the ALTER > DATABASE as the manual suggests. The alternative is to export the > database, drop the current instance, create a new instance > with the correct > characterset and import the old database. > > But I know that there is no data in the database that was not > supported in > the US7ASCII database. Is there a shortcut? The UPDATE > sys.PROPS$ trick > is a no-go. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
