How Locale (Character Set) Choices Affect Your Btrieve Data in Oracle

One of the keys to successfully migrating a Btrieve database to an Oracle backend is proper language settings on the client and in the database so the existing text data are preserved. Refer to this Oracle documentation for more detailed information on globalization and the NLS topic:

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG003

Note: If you are familiar with Oracle's NLS settings, you can skip ahead to Migrating a Btrieve Database.

NLS Parameters

A number of NLS parameters control locale-dependent operations and the ability to store and display multi-lingual information. These include:

1. NLS_LANGUAGE: Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names; for example, January (English) or февраль (Russian).

2. NLS_TERRITORY: Specifies conventions such as the default date, monetary, and numeric formats (for example, whether a comma or a dot is used to separate numbers). If the territory is not specified, then the format is derived from the language value.

3. NLS_CHARACTERSET: Declares what character set scheme (code page) is used for encoding CHAR, VARCHAR2, LONG, and CLOB datatypes.

4. NLS_ NCHAR_CHARACTERSET: Declares what character set scheme (code page) is used for encoding NCHAR and NVARCHAR2 and NCLOB datatypes.

There are three levels at which you can set these NLS parameters:

1. NLS_DATABASE_PARAMETERS: Determined when the CREATE DATABASE statement is issued, they establish the NLS database environment. These parameters are stored in the data dictionary. After the database is created, you cannot change the character set (with a few exceptions) without re-creating the database.

2. NLS_INSTANCE_PARAMETERS: Apply to the Oracle instance. These parameters are read from INIT.ORA or SPFILE at startup.

3. NLS_SESSION_PARAMETERS: Apply to the current session and affect how information is displayed on the client. There are several ways these parameters can be assigned. These are listed below in priority order. Examples are given later in this blog.

a. Explicitly in SQL functions (highest priority)
b. By an ALTER SESSION statement
c. As a Windows System Environment variable NLS_LANG
d. As a Windows Registry entry NLS_LANG
e. Default based on NLS_INSTANCE_PARAMETERS, NLS_DATABASE_PARAMETERS, or the locale setting of your operating system

Oracle NLS Database Parameters

The language (NLS_LANGUAGE) and territory (NLS_TERRITORY) you choose when you create the Oracle database influences the format of day names, date and numeric formats, sort order, etc. They do not affect the ability of the database to store data in a specific language.

The character set you choose does affect the ability of the database to store text in different languages. Oracle recommends the Unicode character set AL32UTF8 for the NLS_CHARACTERSET and AL16UTF16 for the NLS_NCHAR_ CHARACTERSET for all new system deployment. Unicode is a character encoding system that defines every character in most of the spoken languages in the world.

You can display the NLS database parameters:

SELECT * from NLS_DATABASE_PARAMETERS;

Client Environment

The language (NLS_LANGUAGE) and territory (NLS_TERRITORY) you choose for a client affect how day names, dates, numeric formats, and more display on the Oracle client. An example of how these choices affect the client display is shown below. If NLS_ LANGUAGE = AMERICAN and NLS_TERRITORY = AMERICA:

SQL> SELECT Hiredate, ROUND(sal/12,2) sal FROM emp;

If NLS_ LANGUAGE = RUSSIAN and NLS_TERRITORY = RUSSIA

SQL> SELECT Hiredate, ROUND(sal/12,2) sal FROM emp;

Client language and territory settings can be modified on a per session basis using SQL statements or the ALTER SESSION command. For example:

TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
 ALTER SESSION SET NLS_LANGUAGE=German;
 ALTER SESSION SET NLS_TERRITORY=Germany;

Troubleshoot: Connecting Btrieve to an Oracle Database

The NLS_CHARACTERSET you choose affects the ability of the client to properly input and display text in different languages. For example, if you intend to input and display Japanese characters, 文字化け, you need to choose a character set that can encode/decode these characters. If you choose the wrong character set your display will be garbled, and you may see something like âÉÂÌÉÏÔÅËÁ, or � in places where there is no matching character.

Set the system locale to reflect the language for your stored string data.

Control Panel > Regions > Administrative > Change System Locale

When you choose the system locale, the active ANSI code page (ACP) and OEM code page (OEMCP) are assigned and stored in the Windows registry under HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage.

Note: A reboot is required if the system locale is changed.

For example, the system locale chosen above, English (United States), corresponds to ANSI code page 1252 and OEM code page 437.

A system locale of Russian(Russia),

corresponds to active ANSI code page 1251 and OEM code page 866.

Choose the NLS_CHARACTERSET based on the locale. The NLS_CHARACTERSET must be mapped to the active ANSI code page. For example:

This link contains some useful code pages and the corresponding mapping to Oracle character sets:  http://www.sqlsnippets.com/en/topic-13410.html#id-1

Note: NLS_CHARACTERSET cannot be set using ALTER SESSION, it can only be defined as a part of NLS_LANG.

Learn how to easily migrate from Btrieve to SQL.

NLS_LANG is formatted [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]. An example setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252. NLS_LANG is defined as a Windows System Environment variable (takes precedence) or in the registry.

Notes:
* In the registry, there is one NLS_LANG entry for each Oracle Home and the location is specific to the Oracle release.

* Neither NLS_LANGUAGE nor NLS_TERRITORY can be set as "standalone" parameters in the environment or registry on the client. 

* A reboot is required if the value of NLS_LANG is changed.

When NLS_CHARACTERSET is correctly assigned, text data display properly. For example, an appropriate NLS_LANG setting for data stored in Russian is: NLS_LANG = RUSSIAN_RUSSIA.CL8MSWIN1251

SQL> SELECT Name, Hiredate, ROUND(sal/12,2) sal FROM emp;

If the wrong character set is chosen, string data are garbled. For example: NLS_LANG = RUSSIAN_RUSSIA.WE8MSWIN1252

SQL> SELECT Name, Hiredate, ROUND(sal/12,2) sal FROM emp;

See this article for common NLS_LANG settings: https://docs.oracle.com/cd/B19306_01/install.102/b14312/gblsupp.htm

You can check the value of NLS_LANG using SQL*Plus in the Command Prompt:

SQL> host echo %NLS_LANG%

AMERICAN_AMERICA.WE8MSWIN1252
 or
 %NLS_LANG%

In the first case, NLS_LANG is set as an environment variable and its value is displayed. In the second case, NLS_LANG is not defined as an environment variable. If NLS_LANG is not defined as an environment variable you can try:

SQL> @.[%NLS_LANG%].

SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]..sql"
 or
 SP2-0310: unable to open file ".[%NLS_LANG%]."

In the first case, the value for NLS_LANG is set in the Windows registry (to the value between [ and ]). In the second case, NLS_LANG is not even set in the Windows registry.

Success Story: Bank of America Migrates from Btrieve to Oracle With BTR2SQL

Migrating a Btrieve Database

The instructions below explain how to migrate a Btrieve database to an Oracle backend.

1. Create the Oracle Database with the language and territory that reflect how server messages and day and month names are stored, the required sorting sequences, etc. Oracle recommends setting NLS_CHARACTER_SET = AL32UTF8 and NLS_NCHAR_CHARACTER_SET = AL16UTF16 for new deployments.

2. Determine the code page used for storing strings in the Btrieve data file and whether the data are in OEM (MS DOS mode) or ANSI (Windows) format.

3. Set the system locale on your client computer (the computer where you will run the BTR2SQL migration) so the OEM / ANSI code page matches the code page for the Btrieve data file.
Note: If the Btrieve data are stored on a Windows computer, then set the system locale (Control Panel > Regions > Administrative > Change System Locale) on your client computer to match the system locale on the Btrieve computer.

4. Define NLS_LANG to reflect the language and ANSI code page for the migrated data. For example: RUSSIAN_RUSSIA.CL8MSWIN1251. For mapping of code pages to NLS_CHARACTERSET see http://www.sqlsnippets.com/en/topic-13410.html#id-1.

5. Run the DDF Validator. The goal of DDF Validator is to alert you to possible problems in your DDF files and to make suggestion on how to fix them BEFORE migration.

1. Start the BTR2SQL GUI and log in to the target Oracle Database.

2. Select the File.DDF.

3. Choose the file(s) to be converted, then right-click and choose Convert to Oracle.

4. Verify the option settings.
IMPORTANT: Select OEM to ANSI translation if your current data are in OEM format. If the data are stored in OEM format, you must choose the OEM to ANSI conversion during the migration. If you are unsure, choose one file to migrate with this option checked and then browse the converted string data. If string data are garbled try migrating the file again, this time without the option checked.

5. Click OK.

6. Double-click the migrated file to browse the data in the newly create Oracle table. Refer to the Troubleshooting section if string data are garbled.

7. Review the Migration Report.

Runtime Environment

The client runtime environment must match the migration environment for proper display of string data:

• Set the locale (Control Panel > Regions > Administrative > Change System Locale) to match the migration computer setting (for example, Russian(Russia))

• Define NLS_LANG to match the migration computer setting (for example, RUSSIAN_RUSSIA.WE8MSWIN1252)

Troubleshooting

Discussed below are two problems you may encounter with the input or / output display on the client after migration.

1. String data do not display properly when you browse the migrated data in BTR2SQL.

a. Do the active code pages (system locale) on the BTR2SQL client computer match the code page used to store the Btrieve data?

You can verify the active code pages on the BTR2SQL client computer by enabling Basic Runtime tracing in BTR2SQL and then restarting BTR2SQL.

The trace file shows the ANSI and OEM code pages for the BTR2SQL client.

b. Does the character set defined in NLS_LANG properly map to the ANSI code page shown in the trace file?

See NLS_CHARACTERSET for instructions to determine the proper character set. See NLS_LANG for instructions to define and display NLS_LANG.

c. Do string data display correctly when you retrieve records from the newly created Oracle table using SQL *Plus in a Command Prompt?

If so, it is likely your Btrieve data are stored in OEM format and you did not choose the OEM to ANSI Translation option during migration. By default, the Command Prompt displays data using the default OEM code page. Rerun the file conversion, selecting the OEM to ANSI Translation.

2. String data display properly in BTR2SQL but do not display properly when testing using SQL *Plus in the Command Prompt.

a. Do you see  or similar non-printable characters?

Make sure a Unicode friendly font is select in the Command Prompt: right-click the title bar and choose Font > Lucida Console.

b. Do you see ┼ыхэр or other garbled characters?

The Command Prompt defaults to the active OEM code page when it starts. You need to change to the active ANSI code page before testing. You can display the active code page by typing "chcp" at the Command Prompt and the use this same command to change the code page to the active ANSI code page.

Learn how to migrate from Btrieve to SQL without any costly code changes in our free whitepaper.