Oracle12: lpad function does not manage 2 bytes characters as Oracle11

Viewed 99

I'm facing a very strange issue where Oracle 12c is not managing 2 bytes character as Oracle 11g, leading to issues with some functions like LPAD.

We have two databases, one 11g and one 12c, with identical NLS parameters, but while 11g manages cyrillic characters as 1 byte in functions like LPAD, 12c manages them as 2 bytes, leading to problems: if we need a certain value to be 40 chars long, every cyrillic character in it will count as 2 bytes while being padded, but will be displayed as 1 char, meaning that 5 cyrillic characters to be LPADded to 40 will in fact generate a value with length 35.

This behaviour is described in the official Oracle documentation (https://docs.oracle.com/database/121/SQLRF/functions107.htm#SQLRF00663), but it has been so for several versions (including 11g), so it's unclear to me why these 2 versions should have different behaviours with the same settings, and in case, how to manage this.

Important notes:

  1. both databases manage european characters (including special characters from some eastern european alphabets like greek, etc.) and russian characters (cyrillic), so it's not really an option to switch region to "RUSSIA";
  2. using nvarchar2 instead of varchar2 solves the issue (it switches to national charset which is UTF16), but it would imply switching all varchar2 columns in a 4 TB database to nvarchar2, which is quite troublesome and might lead to a LOT of wasted space;
  3. the problem occurs in stored procedures managing data already stored in the database, so this doesn't look like a client misconfiguration.

Database properties for NLS parameters (I've removed date and currency formats since they're not really relevant):

+-----------------------------------+------------+------------+
|   Parameter                       |   12c      |   11g      |
+-----------------------------------+------------+------------+
| NLS_CHARACTERSET                  | AL32UTF8   | AL32UTF8   |
| NLS_COMP                          | BINARY     | BINARY     |
| NLS_DATE_LANGUAGE                 | AMERICAN   | AMERICAN   |
| NLS_ISO_CURRENCY                  | AMERICA    | AMERICA    |
| NLS_LANGUAGE                      | AMERICAN   | AMERICAN   |
| NLS_LENGTH_SEMANTICS              | BYTE       | BYTE       |
| NLS_NCHAR_CHARACTERSET            | AL16UTF16  | AL16UTF16  |
| NLS_NCHAR_CONV_EXCP               | FALSE      | FALSE      |
| NLS_NUMERIC_CHARACTERS            | .,         | .,         |
| NLS_RDBMS_VERSION                 | 12.1.0.2.0 | 11.2.0.4.0 |
| NLS_SORT                          | BINARY     | BINARY     |
| NLS_TERRITORY                     | AMERICA    | AMERICA    |
+-----------------------------------+------------+------------+

V$Parameter properties (same, removed dates):

+-----------------------------------+----------------+----------------+
|   Parameter                       |   12c          |   11g          |
+-----------------------------------+----------------+----------------+
| NLS_COMP                          | BINARY         | BINARY         |
| NLS_DATE_LANGUAGE                 | ENGLISH        | ENGLISH        |
| NLS_ISO_CURRENCY                  | UNITED KINGDOM | UNITED KINGDOM |
| NLS_LANGUAGE                      | ENGLISH        | ENGLISH        |
| NLS_LENGTH_SEMANTICS              | CHAR           | CHAR           |
| NLS_NCHAR_CONV_EXCP               | FALSE          | FALSE          |
| NLS_NUMERIC_CHARACTERS            | .,             | .,             |
| NLS_SORT                          | BINARY         | BINARY         |
| NLS_TERRITORY                     | UNITED KINGDOM | UNITED KINGDOM |
+-----------------------------------+----------------+----------------+

Example from the 12c database:

SELECT 'This is a test данные испытаний' as "Original",
       lpad(nvl('This is a test данные испытаний', ' '), 40) as "LPADded",
       lpad(nvl('данные испытаний', ' '), 40) as "Cyrillic only",
       lpad(nvl('This is a test', ' '), 40) as "Non-cyrillic only",
       lpad(nvl(to_nchar('данные испытаний'), ' '), 40) as "NChar cyrillic only",
       lpad(nvl(to_nchar('This is a test данные испытаний'),
                ' '),
            40) as "NChar mixed"
  FROM dual;

Results:

This is a test данные испытаний           (original - 31 chars)
This is a test данные испыта              (std lpad - 28 chars)
         данные испытаний                 (std lpad cyrillic only - 25 chars)
                          This is a test  (std lpad non-cyrillic only - 40 chars)
                        данные испытаний  (nchar lpad cyrillic only - 40 chars)
         This is a test данные испытаний  (nchar lpad mixed - 40 chars)

In the 11g database, all the above (except, of course, the original) have a length of 40 chars.

Thanks

1 Answers

I think the problem is related to the ambiguous fonts in UNICODE. You can find a description here:

http://unicode.org/reports/tr11/#Ambiguous

In oracle if you use

lengthc function 

always returns the actual length of the character, while the

 lenghtb function 

returns the byte occupation of the character.

A possible solution could be to use the following form:

i tried with UNISTR('\4F4F') that takes up 2 bytes

 select lpad('pippo'||UNISTR('\4F4F'),10+lengthc(UNISTR('\4F4F')),'x') from dual;

and the displayed length is the desired one

Related