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 DGoulet To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @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 DGoulet To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @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
RE: Change Character sets
Yup. That's the ticket. Quintin, Richard To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rquintincc: @vt.edu 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 DGoulet To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @vicr.com cc: Sent
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 Rich.Jesse To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @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
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.1p_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).
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.1p_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
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 DGoulet To: Multiple recipients of list 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 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
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 DGoulet To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @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 DGoulet To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @vicr.com cc: Sent
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 DGoulet To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @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
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 DGoulet To: Multiple recipients of list 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 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
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 DGoulet To: Multiple recipients of list 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 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
RE: Change Character sets
Java drivers seem to require US7ASCII. I'd love to leave it alone. Goulet, Dick DGoulet To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @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 DGoulet To: Multiple recipients of list 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 Sounds to me like someone exported their old database into a new one and now have characters showing up as '?' when
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).
