Forms12c: Unicode Characters Garbled when Insert/Update into NVARCHAR2 in NON-UNICODE Database.

Last updated on SEPTEMBER 02, 2017

Applies to:

Symptoms

Since 11.1.2.x, a new Forms datatype of NCHAR has been introduced. So Forms can handle NCHAR and NVARCHAR data.

Oracle Fusion Middleware Forms Services Deployment Guide 11g Release 2 (11.1.2) E24477-03
2.5 Support for Unicode Columns

However,  if your database character set is non-Unicode Forms tries to INSERT or UPDATE data into unicode columns in the database then those characters will be replaced by the '?' character.

For example, below situation

    - Database setting is below
      NLS_CHARACTERSET JA16SJIS
      NLS_NCHAR_CHARACTERSET AL16UTF16

   - Added NLS_LANG=Japanese_Japan.AL32UTF8 in default.env
      ※Detail is on Online Help(Forms Builder 12c - keyword: NCHAR )

   - Set NCHAR for Data Type

   - Implement below code for WHEN-BUTTON-PRESSED

       e.g.(In this case INS-1)

      INSERT INTO TEST_TABLE(COL_VARCHAR, COL_NVARCHAR2) VALUES(:BLK2.COL_VARCHAR, :BLK2.COL_NVARCHAR2);
      commit;

Press button "INS-1" to insert Characters

Result is characters garbled like [e.g. '?'].

If you use below code on WHEN-BUTTON-PRESSED, Press button "INS-2" to insert Charactors

e.g(In this case INS-2)

    DECLARE
    COL_VARCHAR VARCHAR2(100);
    COL_NVARCHAR NVARCHAR2(100);

    BEGIN
    COL_VARCHAR := :BLK1.TXT_CHAR;
    COL_NVARCHAR := :BLK1.TXT_NCHAR;

    INSERT INTO TEST_NCHAR
    VALUES(COL_VARCHAR, COL_NVARCHAR);

    COMMIT;
    END;

Result is working fine

Cause