Oracle Applications DBA Blog

Changing The Character Set In Oracle Applications

Oracle Applications supports the installation of additional languages along with the base installed languages. In case you are doing the installation of these languages at the time of oracle applications installation rapidwiz will make sure you select a compatible character set. In case you plan to install a new language to an already installed instance of Oracle Applications you need to make sure that the database and application character set support the new language.

Changing the character set of Oracle Applications will happen at two places, firstly at the database level and then at the application file system level.

Although most of the character set conversion have a different set of process to follow which have been individually documented in various metalink notes. In this post i am will talking about the set of steps that generally hold true for most of the cases.

The main steps that would be required to perform the character set conversion in Oracle       Applications are

• Run the Character Set Scanner utility to identify data conversion issues.
• Export the identified objects marked for conversion.
• Perform the Database conversion by user the alter database command.
• Run adadmin to identify and perform the file system character set conversion.

For the sake of ease I am taking a US7ASCII instance running on (you guessed it rite ;) a Redhat Linux and converting it into a UTF8.Although US7ASCII being a binary subset of UTF8 would not really require much of the conversion.

Character Set Scanner utility

Though the change from US7ACII to UTF8 could be done with only using the 'alter database command' we will use the character set scanner utility CSSCAN to scan for any change required.

Installing the CSSCAN schema.

Before using the cssan you must install the schema as a user with DBA privilages.This is done by executing the csminst.sql script located at $ORACLE_HOME/rdbms/admin

cd $ORACLE_HOME/rdbms/admin
$sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Oct 17 16:00:16 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> START csminst.sql

Executing CSSCAN

The CSSCAN executable is located at the $ORACLE_HOME/bin. After executing the script you must connect to the Database as a user with DBA privileges. The CSSCAN utility prompts you with options to scan a table, schema or the enter database. We choose to scan the entire database in our case.

The CSSCAN displays your current character set version and prompts you to enter the new character set version.


$ csscan
Character Set Scanner v1.1 : Release 9.2.0.1.0 - Production on Tue Oct
17 16:03:15 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Username: system
Password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

(1)Full database, (2)User, (3)Table: 1 > 1
Enter new database character set name: > UTF8
Enter number of scan processes to utilize(1..32): 1 >


After the successful completion of the scan three report files will be created scan.txt,scan.out and scan.err.

In my case I have exported the database objects with exceptions as specified in the scan.out and plan to import it back after the database conversion is done.

Changing the Character Set At Database Level

Shutdown all application services and the database and listener.You must do a clean shutdown of your database and start it up in restricted mode before changing the character set.Also you must not have any active sessions before issuing the alter database command.

SQL> alter database character set internal_use UTF8;
alter database character set internal_use UTF8

Import Back Database Objects.

After a successful character set conversion at the database level now you can import back the objects and rebuild the indexes specified the in scan.out report.

Changing the Character Set Application Level

Run adadmin and choose  Maintain Applications Files menu. Under this choose the convert character set conversion option.

Here you will be presented with three choices.

 1. Scan the APPLTOP for exceptions
 2. Scan a CUSTOM directory for exceptions
 3. Convert character set
 4. Return to previous menu


You must choose the first option and after that in case of Custom directory you could choose the second option. This will scan the APPL_TOP and make a list of application files which would require conversion.

After the scan is complete you can choose to convert the character set.

Repeat the above steps on all your APPL_TOPs in case of a multi node instance.