Newsgroup: it.comp.software.database
Titolo: ORACLE: caratteri accentati

Ciao a tutti ho un problema con i caratteri accentati.
Quando faccio un inserimento o un modifica di testo tutti i caratteri
accentati vengono trasformati senza accento.
Qualcuno mi sa aiutare???
Grazie a tutti.

Credo che dipenda dal fatto che il database è stato creato con un set di 
caratteri che non accetta le lettere accentate!
In teoria si dovrebbe ricreare il database per permettere tale inserimento.
Per ora non c'è soluzione!
Mo' cerco e ti faccio sapere se trovo qlcs!
Simone

Subject: Changing the Database Character Set or the Database National
PURPOSE
=======
To explain how to change the database character set or national 
character set
of an existing Oracle8(i) or Oracle9i database without having to recreate
the database.
1. SCOPE & APPLICATION
======================
The method described here is documented in the Oracle 8.1.x and Oracle9i
documentation. It is not documented but it can be used in version 8.0.x.
It does not work in Oracle7.
The database character set is the character set of CHAR, VARCHAR2, LONG,
and CLOB data stored in the database columns, and of SQL and PL/SQL text
stored in the Data Dictionary. The national character set is
the character set of NCHAR, NVARCHAR2, and NCLOB data.
In certain database configurations the CLOB and NCLOB data are stored
in the fixed-width Unicode encoding UCS-2. If you are using CLOB or NCLOB
please make sure you read section "4. HANDLING CLOB AND NCLOB COLUMNS"
below in this document.
Before changing the character set of a database make sure you understand
how Oracle deals with character sets. Before proceeding please refer to
[NOTE:158577.1] "NLS_LANG Explained (How Does Client-Server Character
Conversion Work?)". See also [NOTE:225912.1] "Changing the Database
Character Set - an Overview" for general discussion about various
methods of migration to a different database character set.
If you are migrating from 8.x to 9.x please have a look at
[NOTE:140014.1] "ALERT: Oracle8/8i to Oracle9i Using New "AL16UTF16""
and other referenced notes below.
Before using the method described in this note it is essential to do
a full backup of the database and to use the Character Set Scanner utility
to check your data. See the section "2. USING THE CHARACTER SET SCANNER"
below.
Note that changing the database or the national character set as described
in this document does not change the actual character codes, it only
changes the character set declaration. If you want to convert the contents
of the database (character codes) from one character set to another
you must use the Oracle Export and Import utilities. This is needed,
for example, if the source character set is not a binary subset of
the target character set, i.e. if a character exists in the source
and in the target character set but not with the same binary code.
All binary subset-superset relationships between characters sets
recognized by the Oracle Server are listed in [NOTE:119164.1]
"Changing Database Character Set - Valid Superset Definitions".
Note: The varying width character sets are not supported as national
character sets in Oracle8(i) (see [NOTE:62107.1]). Thus, changing
the national character set from a fixed width character set to
a varying width character set is not supported in Oracle8(i).
For a definition of varying width character sets see section
"4. HANDLING CLOB AND NCLOB COLUMNS" below.
WARNING: Do not use any undocumented Oracle7 method to change
the database character set of an Oracle8(i) or Oracle9i
database. This will corrupt the database.
2. USING THE CHARACTER SET SCANNER
==================================
Character data in the Oracle 8.1.6 and later database versions can be
efficiently checked for possible character set migration problems
with help of the Character Set Scanner utility. This utility is included
in the Oracle Server 8.1.7 software distribution and the newest Character
Set Scanner version can be downloaded from the Oracle Technology Network
site (
http://otn.oracle.com
). The Character Set Scanner on OTN is available
for limited number of platforms only but it can be used with databases
on other platforms in the client/server configuration -- as long as
the database version matches the Character Set Scanner version and
platforms are either both ASCII-based or both EBCDIC-based. It is
recommended to use the newest Character Set Scanner version available
from the OTN site.
The Character Set Scanner is documented in the following manuals:
- "Oracle8i Documentation Addendum, Release 3 (8.1.7)", Chapter 3
- "Oracle9i Globalization Support Guide, Release 1 (9.0.1)", Chapter 10
- "Oracle9i Database Globalization Support Guide, Release 2 (9.2)", 
Chapter 11
Note: The Character Set Scanner coming with Oracle 8.1.7 and Oracle 9.0.1
does not have a separate version number. It reports the database
release number in its banner. This version of the Scanner does not
check for illegal character codes in a database if the FROMCHAR and
TOCHAR (or FROMNCHAR and TONCHAR) parameters have the same value
(i.e. you simulate migration from a character set to itself).
The Character Set Scanner 1.0, available on OTN, reports its version
number as x.x.x.1.0, where x.x.x is the database version number.
This version adds a few bug fixes and it supports FROMCHAR=TOCHAR
provided it is not UTF8.
The Character Set Scanner 1.1, available on OTN and with Release 2 (9.2)
of the Oracle Server, reports its version number as v1.1 followed
by the database version number. This version adds another bug fixes
and the full support for FROMCHAR=TOCHAR.
None of the above versions of the Scanner can correctly analyze CLOB
or NCLOB values if the database or the national character set,
respectively, is multibyte. The Scanner reports such values randomly
as Convertible or Lossy. The version 1.2 of the Scanner will mark
all such values as Changeless (as they are always stored in the Unicode
UCS-2 encoding and thus they do not change when the database or
national character set is changed from one multibyte to another).
Character Set Scanner 2.0 will correctly check CLOBs and NCLOBs for
possible data loss when migrating from a multibyte character set to
its subset.
To verify that your database contains only valid codes, specify the
new database character set in the TOCHAR parameter and/or the new
national character set in the TONCHAR parameter. Specify FULL=Y
to scan the whole database. Set ARRAY and PROCESS parameters
depending on your system's resources to speed up the scanning.
FROMCHAR and FROMNCHAR will default to the original database and
national character sets.
The Character Set Scanner should report only Changless data in
both the Data Dictionary and in application data. If any Convertible
or Exceptional data are reported, the ALTER DATABASE [NATIONAL]
CHARACTER SET statement must not be used without further
investigation of the source and type of these data.
In situations in which the ALTER DATABASE [NATIONAL] CHARACTER SET
statement is used to repair an incorrect database character set
declaration rather than to simply migrate to a new wider character set,
you may be advised by Oracle Support Services analysts to execute
the statement even if Exceptional data are reported.
For more information see also [NOTE:225912.1] "Changing the Database
Character Set - an Overview".
3. CHANGING THE DATABASE OR THE NATIONAL CHARACTER SET
======================================================
Oracle8(i) introduces a new documented method of changing the database
and national character sets. The method uses two SQL statements, which
are described in the Oracle8i National Language Support Guide:
ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>
ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>
The database name is optional. The character set name should be specified
without quotes, for example:
ALTER DATABASE CHARACTER SET WE8ISO8859P1
To change the database character set perform the following steps.
Note that some of them have been erroneously omitted from the Oracle8i
documentation:
1. Use the Character Set Scanner utility to verify that your database
contains only valid character codes -- see "2. USING THE CHARACTER SET
SCANNER" above.
2. If necessary, prepare CLOB columns for the character set change
-- see "4. HANDLING CLOB AND NCLOB COLUMNS" below.
Omitting this step can lead to corrupted CLOB/NCLOB values
in the database.
If SYS.METASTYLESHEET (STYLESHEET) is populated (9i and up only)
then see [NOTE:213015.1] "ORA-12716 SYS.METASTYLESHEET (STYLESHEET)
Converting Characterset" for the actions who need to be taken.
3. Make sure the parallel_server parameter in INIT.ORA is set to false
or it is not set at all.
4. Execute the following commands in Server Manager (Oracle8) or sqlplus
(Oracle9), connected as INTERNAL or "/ AS SYSDBA":
SHUTDOWN IMMEDIATE; -- or NORMAL
<do a full database backup>
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET <new_character_set>;
SHUTDOWN IMMEDIATE; -- OR NORMAL
STARTUP RESTRICT;
5. Restore the parallel_server parameter in INIT.ORA, if necessary.
6. Execute the following commands:
SHUTDOWN IMMEDIATE; -- OR NORMAL
STARTUP;
The double restart is necessary in Oracle8(i) because of
a SGA initialization bug, fixed in Oracle9i.
7. If necessary, restore CLOB columns -- see "4. HANDLING CLOB AND NCLOB
COLUMNS" below.
To change the national character set replace the ALTER DATABASE 
CHARACTER SET
statement with ALTER DATABASE NATIONAL CHARACTER SET. You can issue both
statements together if you wish.
Error Conditions
----------------
A number of error conditions may be reported when trying to change the 
database
or national character set.
In Oracle8(i) the ALTER DATABASE [NATIONAL] CHARACTER SET statement will 
return:
ORA-01679: database must be mounted EXCLUSIVE and not open to activate
- if you do not enable restricted session
- if you startup the instance in PARALLEL/SHARED mode
- if you do not set the number of queue processes to 0
- if you do not set the number of AQ time manager processes to 0
- if anybody is logged in apart from you.
This error message is misleading. The command requires the database to be
open but only one session, the one executing the command, is allowed.
For the above error conditions Oracle9i will report one of the errors:
ORA-12719: operation requires database is in RESTRICTED mode
ORA-12720: operation requires database is in EXCLUSIVE mode
ORA-12721: operation cannot execute when other sessions are active
Oracle9i can also report:
ORA-12718: operation requires connection as SYS
if you are not connect as SYS (INTERNAL, "/ AS SYSDBA").
If the specified new character set name is not recognized, Oracle will 
report
one of the errors:
ORA-24329: invalid character set identifier
ORA-12714: invalid national character set specified
ORA-12715: invalid character set specified
The ALTER DATABASE [NATIONAL] CHARACTER SET command will only work if
the old character set is considered a binary subset of the new character 
set.
Oracle Server 8.0.3 to 8.1.5 recognizes US7ASCII as the binary subset of
all ASCII-based character sets. It also treats each character set as
a binary subset of itself. No other combinations are recognized.
Newer Oracle Server versions recognize additional subset/superset
combinations, which are listed in [NOTE:119164.1].
If the old character set is not recognized as a binary subset of
the new character set, the ALTER DATABASE [NATIONAL] CHARACTER SET
statement will return:
- in Oracle 8.1.5 and above:
ORA-12712: new character set must be a superset of old character set
- in Oracle 8.0.5 and 8.0.6:
ORA-12710: new character set must be a superset of old character set
- in Oracle 8.0.3 and 8.0.4:
ORA-24329: invalid character set identifier
It may be necessary to switch off the superset check to allow changes
between formally incompatible character sets to solve certain character set
problems or to speed up migration of huge databases. Oracle Support Services
may pass the necessary information to customers after verifying the safety
of the change for the customers' environments.
4. HANDLING CLOB AND NCLOB COLUMNS
==================================
Background
----------
In a fixed width character set codes of all characters have the same
number of bytes. Fixed width character sets are: all single-byte
character sets and those multibyte character sets which have names
ending with 'FIXED'. In Oracle9i the character set AL16UTF16 is also
fixed width.
In a varying width character set codes of different characters may
have different number of bytes. All multibyte character sets except
those with names ending with FIXED (and except Oracle9i AL16UTF16
character set) are varying width.
Single-byte character sets are character sets with names of the form
xxx7yyyyyy and xxx8yyyyyy. Each character code of a single-byte
character set occupies exactly one byte. Multibyte character sets
are all other character sets (including UTF8). Some -- usually most
-- character codes of a multibyte character set occupy more than
one byte.
CLOB values in a database whose database character set is fixed width
are stored in this character set.
CLOB values in an Oracle 8.0.x database whose database character set
is varying width are not allowed. They have to be NULL.
CLOB values in an Oracle >= 8.1.5 database whose database character set
is varying width are stored in the fixed width Unicode UCS-2 encoding.
The same holds for NCLOB values and the national character set.
The UCS-2 storage format of character LOB values, as implemented
in Oracle8i, ensures that calculation of character positions in
LOB values is fast. Finding the byte offset of a character stored
in a varying width character set would require reading the whole LOB
value up to this character (possibly 4GB). In the fixed width
character sets the byte offsets are simply character offsets
multiplied by the number of bytes in a character code.
In UCS-2 byte offsets are simply twice the character offsets.
As the Unicode character set contains all characters defined in any other
Oracle character set, there is no data loss when a CLOB/NCLOB value
is converted to UCS-2 from the character set in which it was provided
by a client program (usually the NLS_LANG character set).
CLOB Values and the Database Character Set Change
-------------------------------------------------
In Oracle 8.0.x CLOB values are invalid in varying width character sets.
Thus you must delete all CLOB column values before changing the database
character set to a varying width character set.
In Oracle 8.1.5 and later CLOB values are valid in varying width
character sets but they are converted to Unicode UCS-2 before being
stored. But UCS-2 encoding is not a binary superset of any other
Oracle character set. Even codes of the basic ASCII characters are
different, e.g. single-byte code for "A"=0x41 becomes two-byte
code 0x0041. This implies that even if the new varying width
character set is a binary superset of the old fixed width
character set and thus VARCHAR2/LONG character codes remain valid,
the fixed width character codes in CLOB values will not longer
be valid in UCS-2.
As mentioned above, the ALTER DATABASE [NATIONAL] CHARACTER SET
statement does not change character codes. Thus, before changing
a fixed width database character set to a varying width character set
(like UTF8) in Oracle 8.1.5 or later, you first have to export
all tables containing non-NULL CLOB columns, then truncate these
tables, then change the database character set and, finally, import
the tables back to the database. The import step will perform
the required conversion.
If you omit the steps above, the character set change will succeed
in Oracle8(i) (Oracle9i disallows the change in such situation)
and the CLOBs may appear to be correctly legible but as their
encoding is incorrect, they will cause problems in further
operations. For example, CREATE TABLE AS SELECT will not correctly
copy such CLOB columns. Also, after installation of the 8.1.7.3
server patchset the CLOB columns will not longer be legible.
LONG columns are always stored in the database character set and
thus they behave like CHAR/VARCHAR2 in respect to the character set
change. BLOBs and BFILEs are binary raw datatypes and their
processing does not depend on any Oracle character set setting.
NCLOB Values and the National Character Set Change
--------------------------------------------------
The above discussion about changing the database character set
and exporting and importing CLOB values is theoretically applicable
to the change of the national character set and to NCLOB values.
But as varying width character sets are not supported as national
character sets in Oracle8(i), changing the national character set
from a fixed width character set to a varying width character set
is not supported at all.
Preparing CLOB Columns for the Character Set Change
---------------------------------------------------
Take a backup of the database.
If using Advanced Replication or deferred transactions functionality,
make sure that there are no outstanding deferred transactions with
CLOB parameters, i.e. DEFLOB view must have no rows with non-NULL
CLOB_COL column; to make sure that replication environment remains
consistent use only recommended methods of purging deferred
transaction queue, preferably quiescing the replication environment.
Then:
- If changing the database character set from a fixed width
character set to a varying with character set in Oracle 8.0.x,
set all CLOB column values to NULL -- you are not allowed
to use CLOB columns after the character set change.
- If changing the database character set from a fixed width
character set to a varying width character set in Oracle 8.1.5
or later, perform table-level export of all tables containing
CLOB columns, including SYSTEM's tables. Set NLS_LANG
to the old database character set for the Export utility.
Then truncate these tables.
Restoring CLOB Columns after the Character Set Change
-----------------------------------------------------
In Oracle 8.1.5 or later, after changing the character set as described
above (steps 3. to 6.), restore CLOB columns exported in step 2.
by importing them back into the database. Set NLS_LANG to the old
database character set for the Import utility to avoid IMP-16 errors
and data loss.
RELATED DOCUMENTS:
==================
[NOTE:13856.1] V7: Changing the Database Character Set
-- This note has limited distribution, please contact Oracle Support
[NOTE:62107.1] The National Character Set in Oracle8
[NOTE:119164.1] Changing Database Character set - Valid Superset definitions
[NOTE:118242.1] ALERT: Changing the Database or National Character Set
Can Corrupt LOB Values
<Note.158577.1> NLS_LANG Explained (How Does Client-Server Character 
Conversion Work?)
[NOTE:140014.1] ALERT: Oracle8/8i to Oracle9i using New "AL16UTF16"
[NOTE:159657.1] Complete Upgrade Checklist for Manual Upgrades from 8.X 
/ 9.0.1 to Oracle9i (incl. 9.2)
Oracle8i National Language Support Guide
Oracle8i Release 3 (8.1.7) Readme
- Section 18.12 "Restricted ALTER DATABASE CHARACTER SET Command Support
(CLOB and NCLOB)"
Oracle8i Documentation Addendum, Release 3 (8.1.7)
- Chapter 3 "New Character Set Scanner Utility"
Oracle8i Application Developer's Guide - Large Objects (LOBs), Release 2
- Chapter 2 "Basic Components"
Oracle8 Application Developer's Guide, Release 8.0
- Chapter 6 "Large Objects (LOBs)", Section "Introduction to LOBs"
Oracle9i Globalization Guide, Release 1 (9.0.1)
Oracle9i Database Globalization Guide, Release 2 (9.2)
For further NLS / Globalization information you may start here:
[NOTE:150091.1] Globalization Technology (NLS) Library index
.
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal 
Notices and Terms of Use.

Grazie mille....
Avevo fatto delle ricerche sul sito della oracle, ma probabilmente sbagliavo
i criteri.
Grazie ancora.
"Simone Sarzano" <sim...@sarzano.com.invalid> ha scritto nel messaggio
news:b48bm4$m07$1...@lacerta.tiscalinet.it...
