free Oracle DBA tutorial Oracle Jobs
Ask A Question
SQL Statement Tuning
Backup and Recovery Concepts
Oracle 11g New Features
Oracle E Suite & Others
Oracle Data Guard
Oracle DBA FAQ

Customizing Locale Data

Previous Chapter

Customizing Character Sets
After a locale definition file is created using the Locale Builder, it must be compiled into platform-specific binary files that can be dynamically loaded into memory at runtime. The NLS Data Installation Utility (lxinst) described in this chapter allows Oracle DBA to convert and install locale definition text files into binary format, and merge it into an NLS data object set.

Character Set Customization Example
This section uses an example to introduce the steps required to create a new character set. For this example, we will create a new character set based on Oracle's JA16EUC character set and add a few user-defined characters.

Be aware of the following limitations:

* Input of user-defined characters must still be managed by the system, either through an input method or a virtual keyboard.

* Display of user-defined characters must still be managed by the system or the application. In the case of display, a new font specification may be needed. Many vendors provide support of a font editor. After a new font is created, it must be installed onto your system and made accessible to application programs.

Step 1. Back up the NLS binary boot files

Oracle recommends that Oracle DBA backup the NLS installation boot file (lx0boot.nlb) and the NLS system boot file (lx1boot.nlb) in the ORA_NLS33 directory before generating and installing NLB files. Enter the following commands:

% cd $ORA_NLS33

% cp lx0boot.nlb lx0boot.nlb.orig

% cp lx1boot.nlb lx1boot.nlb.orig

Step 2. Generate and install the NLB files

Now Oracle DBA are ready to generate and install the new NLB files. The NLB files are platform-dependent, so regenerate them on each platform and also install these files on both the server and clients.

Use the lxinst utility or the Locale Builder to create both the binary character definition files (lx2dddd.nlb) and update the NLS boot file (lx*boot.nlb).

Example lxinst Example

The lxinst utility uses the existing system boot file. Therefore, copy the existing binary system boot file into the directory specified by SYSDIR. For this example, define SYSDIR to be the working directory (/tmp). Enter the following command:

% cp lx1boot.nlb /tmp

The new character set definition file (lx22710.nlt) and the text boot file containing the new character set entry (lx0boot.nlt) that was created in Steps 2 and 3 should reside in the directory specified by ORANLS. For this example, specify it to be /tmp. Also, since we define BASE_CHAR_SET (the base definition file) to be JA16EUC (Id 830 in hex value 033e), its NLT file (lx2033e.nlt) or NLB file (lx*033e.nlb) should also be in the directory specified by ORANLS, so that the new character set can inherit all definitions from it.Enter one of the following commands:

% cp lx2033e.nlt /tmp


% cp lx*033e.nlb /tmp

Use the lxinst utility to generate an NLB file (lx22710.nlb) for the character set in the directory specified by the ORANLS and an updated binary boot file (lx1boot.nlb) in the directory specified by DESTDIR. For this example, define ORANLS, SYSDIR, and DESTDIR all to be /tmp. Enter the following command:

% $ORACLE_HOME/bin/lxinst oranls=/tmp sysdir=/tmp destdir=/tmp

Install the newly generated binary boot file (lx1boot.nlb) into the ORA_NLS33 directory:

% cp /tmp/lx1boot.nlb $ORA_NLS33/lx1boot.nlb

Finally, install the new character set definition file (lx2*.nlb) into the ORA_NLS33 directory. If there are files with names similar to lx5*.nlb or lx6*.nlb, then install them, too:

% cp /tmp/lx22710.nlb $ORA_NLS33

% cp /tmp/lx52710.nlb $ORA_NLS33

% cp /tmp/lx62710.nlb $ORA_NLS33

Step 3. Repeat for Each Platform

Oracle DBA must repeat Step 2 on each hardware platform because the NLB file is a platform-specific binary. It must also be repeated for every system that must recognize the new character set. Therefore, Oracle DBA should compile and install the new NLB files on both server and client machines.

Step 4. Create the Database Using New Character Set

After installing the NLB files, shut down and restart the database server in order to initialize NLS data loading.

After bringing the database server back up, create the new database using the newly created character set.

To use the new character set on the client side, simply exit the client (such as Enterprise Manager or SQL*Plus) and re-invoke it after installing the NLB files.

Creating Character Set Definition Files
If Oracle DBA have any Java products (for example, JDBC or SQLJ) in your applications and want them to support user-defined characters, Oracle DBA must generate and install a special Java zip file (gss_custom.zip) into your Oracle Home directory. The installation steps are as follows:


$ORACLE_HOME/JRE/bin/jre -classpath $ORACLE_HOME/jlib/gss-1_1.zip:$ORACLE_
HOME/jlib/gss_charset-1_2.zip Ginstall .nlt

On Windows:

%JREHOME%\bin\jre.exe -classpath %ORACLE_HOME%/jlib/gss-1_1.zip;%ORACLE_
HOME%/jlib/gss_charset-1_2.zip Ginstall .nlt

where %JREHOME% is the C:\Program Files\Oracle\jre\version_num directory.

lx22710.nlt is an example of an user-defined character set created using the Oracle Locale Builder.

The above commands generate a gss_custom.zip file in the current directory. If Oracle DBA need to add support for more than one user-defined character set, Oracle DBA can append their definitions to the same gss_custom.zip file by re-issuing the above command for each of the additional user-defined character sets. For example:

$ORACLE_HOME/JRE/bin/jre -classpath $ORACLE_HOME/jlib/gss-1_1.zip:
$ORACLE_HOME/jlib/gss_charset-1_2.zip Ginstall .nlt

$ORACLE_HOME/JRE/bin/jre -classpath $ORACLE_HOME/jlib/gss-1_1.zip:
$ORACLE_HOME/jlib/gss_charset-1_2.zip Ginstall .nlt

$ORACLE_HOME/JRE/bin/jre -classpath $ORACLE_HOME/jlib/gss-1_1.zip:
$ORACLE_HOME/jlib/gss_charset-1_2.zip Ginstall .nlt

lx22710.nlt, lx22711.nlt and lx22712.nlt will all be contained in gss_custom.zip.

After gss_custom.zip has been created, store it in the
$ORACLE_HOME/ocommon/nls/admin/data directory. For example:

% cp gss_custom.zip $ORACLE_HOME/ocommon/nls/admin/data

Enabling Java and User-Defined Character Sets
There are three Java components where you may want to add the gss_custom.zip file:

* Java Virtual Machine (for Java in the database)


%loadjava -u sys/ -grant EXECUTE -synonym -r -r -v gss_custom.zip

On Windows:

loadjava -u sys/ -grant EXECUTE -synonym -r -r -v gss_custom.zip

This loads it into the database. Note that needs to be replaced by the password for SYS. * Apache (for using servlets)

Edit the file jserv.properties as follows:

On UNIX, add the line:

wrapper.classpath = $ORACLE_HOME/ocommon/nls/admin/data/gss_custom.zip

On Windows, add the line:

wrapper.classpath = %ORA_HOME%\ocommon\nls\admin\data\gss_custom.zip

* Client environment (for JDBC on the client)


%set CLASSPATH $ORACLE_HOME/ocommon/nls/admin/data/gss_custom.zip

On Windows:

Add the path %ORACLE_HOME%\ocommon\nls\admin\data\gss_custom.zip to your existing CLASSPATH.

Customizing Time Zone Data
The time zone files contain the valid time zone names. The following information is included for each zone:

* Offset from UTC (Coordinated Universal Time)

* Transition times for daylight savings

* Abbreviations for standard time and daylight savings time

Abbreviations are only used in conjunction with the zone names. There are 2 timezone files under the Oracle installation directory (ORACLE_HOME):

* oracore/zoneinfo/timezone.dat

This is the default. It contains the most commonly used time zones and is smaller for better database performance.

* oracore/zoneinfo/timezlrg.dat

This file contains the larger set of defined time zones and should be used by customers who require time zones that are not defined in the default timezone.dat file. This larger set of time zone information might affect performance.

To enable the use of the larger time zone data file, you must:

1. Shut down the database.

2. Set the ORA_TZFILE environment variable to the full pathname of the location for the timezlrg.dat file.

3. Restart the database.

After the larger timezlrg.dat is used, it must continue to be used unless the user is sure that none of the non-default zones are used for data that is stored in the database. Also, all databases that share information must use the same timezone data file.

To view the timezone names, issue the following query:


Customizing Calendars
A number of calendars besides Gregorian are supported. Although all of them are defined with data linked directly into NLS, some of them may require the addition of ruler eras (in the case of imperial calendars) or deviation days (in the case of lunar calendars) in the future. In order to do this without waiting for a new release, you can define the additional eras or deviation days in an external file, which is then automatically loaded when executing the calendar functions.

The calendar data is first defined in a text-format definition file. This file must be converted into binary format before it can be used. The Calendar Utility described here allows you to do this.

NLS Calendar Utility

The Calendar Utility takes as input a text-format definition file. The name of the file and its location are hard-coded as a platform-dependent value. On UNIX platforms, the file name is lxecal.nlb, and its location is $ORACLE_HOME/ocommon/nls. A sample calendar definition file is included in the distribution.

The lxegen executable produces as output a binary file containing the calendar data in the appropriate format. The name of the output file is also hard-coded as a platform-dependent value. On UNIX, the name is lxecal.nlb The file will be generated in the same directory as the text-format file, and an already-existing file will be overwritten.

Once the binary file has been generated, it will automatically be loaded during system initialization. Do not move or rename the file, as it is expected to be found in the same hard-coded name and location.


The Calendar Utility is invoked directly from the command line:


Previous Chapter

More Tutorials on Oracle dba ...

Source :Oracle Documentation

Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info