Consultor Eletrônico

Status: Unverified

GOAL:

How to debug issues displaying extended characters with Oracle dataserver?

FIX:

This solution serves as a guide to help troubleshoot the problem of not displaying extended characters, for example, data beginning with extended characters does not get selected in reports with "open ranges".

Here's a quick list of things to try :

Before starting, make sure everything (Oracle, schema holder, and progress client) agrees on the appropriate code page, case conversion tables and collation tables. Please reference internationalization Guide and Chapter 3.5 of Progress DataServer for ORACLE Guide for detailed information. If any of these items need to be changed, restart from zero:recreate both oracle table and schema holder, re-insert test row, please do not use a .d or another load method.

In following steps, # is used to represent a lowercase 'o' with an umlaut.

1. Create an empty progress database with appropriate code page , set code page, case conversion table and collation tables when starting a progress client.

2. Create a single table "foo" with three character
columns (called A, B, and C) with the following properties:

A case insensitive, indexed (define a single unique index on A)
B case insensitive, not indexed
C case sensitive.

3. Migrate to ORACLE. Table in ORACLE Should have 5 columns (U##A,
A, B, C, and progress_recid). Verify this in sqlplus with 'describe FOO'

4. In sql plus, perform the following tests:

insert into foo values (UPPER('#'), '#', '#', '#',
foo_seq.nextval);

select count (*) from foo where U##a = UPPER('#');
select count (*) from foo where a = '#';
select count (*) from foo where b = '#';
select count (*) from foo where c = '#';

Each select should return 1.

select dump(U##A, 10) from foo;
select dump(A, 10) from foo;

these two select statements will give the ascii value ORACLE is
using for both upper('#') and '#'.

5. Also in sqlplus try:

select count (*) from foo where U##a between upper('n') and upper('p');
select count (*) from foo where a between 'n' and 'p';
select count (*) from foo where b between 'n' and 'p';
select count (*) from foo where c between 'n' and 'p';


5A. Does placing the NLS_SORT funcion around the terms change the
behavior? As in:
select count (*) from foo where NLS_SORT(a) between
NLS_SORT('n') and NLS_SORT('p');
See also item 9.

6. Perform similar select count(*) statements using the progress
editor, when connected to ORACLE. You won't be able to reference
the column U##a, so skip that one.

7. In sqlplus, truncate table foo. Redo everything, except use
NLS_UPPER in place of UPPER in all the previous sql (both the
inserts and selects) executed in sqlplus

8. In sqlplus:

select upper('#') from dual
select dump('#', 16) from dual
select dump(upper('#'), 16) from dual

select nls_upper('#') from dual
select dump(nls_upper('#'), 16) from dual

9. Does issuing 'alter session set nls_sort = XGerman' (in sqlplus,
first thing) change any of the behavior?