Change datatype VARCHAR2 to NVARCHAR2 when it's uploading

  • Document ID:KM03606126
  • Creation Date:06-Mar-2020
  • Modified Date:06-Mar-2020
    • Micro Focus Products:
      structured data manager

This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.

Summary

A customer needs to change datatype VARCHAR2 to NVARCHAR2 to handle multibyte characters during upload to Oracle.

Error

ERROR

Caused by: java.sql.BatchUpdateException: ORA-12899: value too large for column "EXPADMIN"."ATTACHMENT_PATHS"."DESCRIPTION" (actual: 32, maximum: 25)
ORA-06512: at "EXPADMIN.SP_ATTACHMENT_PATHS1798711986", line 7
ORA-06512: at line 1

Cause

Cause

The upload files as destination colums does not have the appropiate size, as soon the size is change the issue goes away

Fix

Fix

Steps to map datatype varchar2 to nvarchar2 during upload to oracle :

Case 1 : If your D2F environment ( source DB ) is other than Oracle

             1) Open (SDM_INSTALL_DIR)/obt/foundation/components/config/dataTypeMapping/oracle.xml

             2) Add a simple mapping from varchar2 to nvarchar2 to the file as below,

                  <mapping>
                  <columnInput>VARCHAR2</columnInput>
                  <columnOutput length="80">NVARCHAR2</columnOutput>
                  </mapping>

                You can explicitly define the length for nvarchar2 type to store multibyte characters.

            3) Do create a mapping upload file for mapping schemas between source and target database.

            4) Perform the upload to oracle, you'll now have all the columns which were of type varchar2 in source DB changed to type nvarchar having                      length 80.

           Also, instead of just simple mapping you can have database specific mapping & also Auto-expansion mapping based on some rules.

Case 2 : If your D2F environment is Oracle

             1) If your source DB is oracle and your upload location is also oracle, then the mapping wont be picked from the oracle.xml file instead you                       need to define the mapping in the upload schema mapping file itself and this mapping is done at the individual column level.

             2) For example if you want to map a column of type varchar2 to nvarchar2 make the following changes to upload mapping file,

                 DEMARC_schema=UPLOAD_schema

                 DEMARC_schema.BUNIT.BUNAME.VARCHAR2=UPLOAD_schema.BUNIT.BUNAME.NVARCHAR2[length:80]

          

               where DEMARC_schema & UPLOAD_schema are source & target schemas,

                         BUNIT - table name,

                         BUNAME - column name

           3) Perform the upload to oracle, you'll now have the column type of BUNAME changed to nvarchar2 with length 80.

You can include as many columns as you wish to map in this mapping file (one below the other).