Character set query in Oracle12c

character-setoracleoracle-12c

I have created my Oracle 12c database with database character set as AL32UTF8 and I have been told by the developers that it needs to be changed to UTF8.
National character set is UTF8.

Is it supported and feasible conversion of character set from AL32UTF8 to UTF8?

Best Answer

In Oracle 12c you should use the Database Migration Assistant for Unicode (DMU) to perform this operation.

https://docs.oracle.com/database/121/DUMAG/toc.htm

Your proposed conversion is very straightforward and shouldn't be a problem, however: Are you sure this is what you want to do? Oracle recommends AL32UTF8 above UTF-8, unless it is absolutely neccessary:

Oracle supports two encodings of Unicode as the database character set: UTF-8 through the AL32UTF8 character set and CESU-8 through the UTF8 character set. (Note the use of the hyphen in the name of Unicode encoding and the lack of a hyphen in the name of Oracle character set. This differentiation is used throughout Oracle documentation.) UTF-8 is a multibyte varying width Unicode encoding using 1 to 4 bytes per character. CESU-8 is a compatibility-only encoding, discouraged for information exchange by the Unicode standard. CESU-8 is very similar to UTF-8 except that so-called supplementary characters encoded by UTF-8 in 4 bytes, CESU-8 encodes as pairs of 3-byte codes. Oracle Database has deprecated the UTF8 character set, so it should not be used as a database character set except when explicitly required by an application, such as Oracle E-Business Suite Release 11i.

Related Solutions

Oracle Import problem caused by different character sets

If that is the actual DDL you are using to create the table, you could use the NLS_LENGTH_SEMANTICS parameter. If you set that to CHAR rather than the default of BYTE, a VARCHAR2(5) will be allocated enough space to store 5 characters in the database character set (potentially up to 20 bytes) rather than 5 bytes (which could allow just 1 character).

Unfortunately, changing the NLS_LENGTH_SEMANTICS probably won't be terribly helpful if you're relying on the import process to create the table-- the dump file will inherently add the CHAR or BYTE keyword so it would actually issue the statement

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3 BYTE)  NOT NULL,
    Name                  VARCHAR2(60 BYTE) NOT NULL,
    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL
);

Oracle XE 11.2 Export / Import Charset

The export utility will use the NLS_LANG environment variable specified for the client session. If all your data can be represented in the Windows-1252 character set, that shouldn't be an issue. If you want to do the export using the AL32UTF8 character set, you'd need to set the NLS_LANG. In Windows, that would be something like

c:\> set nls_lang = american_america.al32utf8

Best Answer

Related Solutions

Oracle Import problem caused by different character sets

Oracle XE 11.2 Export / Import Charset

Related Question