Re: help !! urgent !! change from we8dec to we8iso8859p1

In a verion 7 database you can use the following method (from Oracle Support)

Regards

Alex

Subject: V7: CHANGING DATABASE CHARACTER SET
>
> Article-ID: <Note:13856.1>
> Alias: OLS:105600.874
> Circulation: PUBLISHED (EXTERNAL)
> Folder: NLS
> Topic: * Using NLS
> Platform: GENERIC Generic issue
> Subject: V7: CHANGING DATABASE CHARACTER SET
> Modified-Date: 14-AUG-1998 08:40:58
> Document-Type: BULLETIN
> Impact: MEDIUM
> Skill-Level: ACCOMPLISHED
> Component: RDBMS Server 07
> Content-Type: TEXT/PLAIN
> Attachments: NONE
>
> Introduction
> ~~~~~~~~~~~~
> This article describes how one can change the character set of a
> database.
> It should be done with extreme caution having noted the following
> limitations:
>
> * This is not an officially supported method of changing the
> characterset.
> You should only be using this bulletin if Oracle Support have
> confirmed
> the steps are valid for the character set change you wish to
> perform.
>
> * Do NOT attempt to use the steps here against an Oracle8 database.
> Doing so will raise an internal error:
> ORA-00600: internal error code, arguments: [adbdrv2], []
> and further use of the database could result in unrecoverable
> corruption.
>
> * The database character set should only be changed if the characters
> of the
> code page of the originating database correspond to the same
> characters
> of the target database, ie. if the database was created with the
> characterset US7ASCII and it is to be updated to WE8ISO8859P1.
> Since these
> have the same encoding scheme for the first 127 bits, changing the
> character set from US7ASCII to WE8ISO8859P1 will display all
> characters up
> to 127 bits as the same character before and after. In addition, in
> this
> particular example, if any characters have been entered with the 8th
> bit
> set, then updating the database characterset to 8 bit will allow
> that 8th
> bit to be displayed. You must not change the characterset from one
> encoding
> scheme to another encoding scheme where the code pages do not
> correspond.
> This will completely scramble your database. In addition, if
> case*designer
> diagrams are stored in the database, this method must not be used.
> Contact
> Worldwide Support for further details.
>
> * Before attempting to run any of the scripts below, you must take a
> full
> cold backup of your database. In addition, the procedure must be
> thoroughly
> tested before attempting this on a production instance.
>
> Changing the Character Set
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> Here is a SQL*PLUS script that allows a database character set to be
> changed to a different encoding scheme without having to rebuild the
> database.
> set echo off
> set verify off
>
> rem The data dictionary table that records the database
> rem character set is sys.props$
> rem
> rem SQL> describe sys.props$
> rem Name Null? Type
> rem ------------------------------- -------- ----
> rem NAME NOT NULL VARCHAR2(30)
> rem VALUE$ VARCHAR2(2000)
> rem COMMENT$ VARCHAR2(2000)
>
> rem For example:
> rem
> rem SQL> column c1 format a30
> rem SQL> select name c1, value$ c1 from sys.props$;
>
> rem C1 C1
> rem ------------------------------ ------------------------------
> rem DICT.BASE 2
> rem NLS_LANGUAGE AMERICAN
> rem NLS_TERRITORY AMERICA
> rem NLS_CURRENCY $
> rem NLS_ISO_CURRENCY AMERICA
> rem NLS_NUMERIC_CHARACTERS .,
> rem NLS_DATE_FORMAT DD-MON-YY
> rem NLS_DATE_LANGUAGE AMERICAN
> rem NLS_CHARACTERSET WE8DEC
> rem NLS_SORT BINARY
> rem GLOBAL_DB_NAME NLSV7.WORLD
>
> rem NLS_CHARACTERSET can be changed by updating its value, for
> example:
>
> rem update sys.props$
> rem set value$ = 'WE8ISO8859P1'
> rem Where name = 'NLS_CHARACTERSET';
>
> rem The database has to be shutdown and restarted before the change
> rem becomes effective.
>
> rem It is very important to specify the character set name correctly.
> rem IMPORTANT NOTE
> rem =============
> rem If NLS_CHARACTERSET is updated to an invalid value, it will not
> then
> rem be possible to restart the database once it has been shutdown.
> rem To recover, it will be necessary to re-create the database, since
> it
> rem cannot be restarted to correct the invalid NLS_CHARACTERSET entry.
>
> rem The character set name should be in uppercase.
> rem The new value is not effective until the database has been
> shutdown and
> rem restarted.
> rem
> rem A suggested procedure is as follows, and can be done by running
> this
> rem script from SQL*Plus when logged into the SYSTEM account.
> rem
> rem USAGE : SQL> start ch_db.sql <character set>
> rem
> rem where <character set> is the desired database character set
> rem
>
> Prompt First check that the character set name is valid.
>
> set echo on
>
> select convert('a','&1','us7ascii') from dual;
>
> set echo off
>
> prompt If this select statement returns error ORA-01482, then the
> prompt specified character set name is not valid for this
> installation.
> prompt Abort the procedure now with Control-c
>
> prompt To continue, press return
> accept ans CHAR
>
> Prompt Check the current value of database character set.
>
> column c1 format a30
> select name c1, value$ c1 from sys.props$
> where name = 'NLS_CHARACTERSET';
>
> prompt To continue, press return
>
> Prompt Update to new character set
>
> update sys.props$
> set value$ = upper('&1')
> where name = 'NLS_CHARACTERSET';
>
> set echo off
>
> prompt To continue, press return
> accept ans CHAR
>
> Prompt Check the new value of database character set
>
> select name c1, value$ c1 from sys.props$
> where name = 'NLS_CHARACTERSET';
>
> Prompt If the value is updated as required, press return to continue
> and
> Prompt then manually type COMMIT; to commit the change. Then shutdown
> and
> Prompt restart the database.
> Prompt
> Prompt If the value is not updated as required, press return to
> continue and
> Prompt than manually type ROLLBACK; to prevent the change.
>
> prompt To continue, press return
> accept ans CHAR
>
> ----------------------------------------------------------------------
> --

Ulrich Wald wrote:

> Hi David,
>
> the only way to set the 'character set' is in the 'create database' command.
> it seems to me that you didn't set the parameter to we8iso8859p1 and so the
> database
> is set to the default of 'we8dec'.
> So i think you have to create a new database to set the correct character set.
>
> Ulli
>
> David Novo wrote:
>
> > Anybody can say me how can I change the default character of my database
> > from we8dec to we8iso8859p1 ¿?
> >
> > I can't do it with NLS_LANG american_america.we8iso8859p1 because an error
> > 12705 invalid parameter always appear when I execute the application.
> >
> > I need a solution !!
> >
> > Thanks, David
Received on Tue Feb 09 1999 - 11:47:49 CET