|Product:||Syniti Data Replication, DBMoto|
|Summary:||When replicating to Oracle targets, invalid number error with decimal precision greater than 28 using different language settings|
When replicating to Oracle in a numeric/decimal field with precision greater than 28, and the servers involved have different language settings, the following issue may occur. Decimal fields with precision greater than 28 are handled as strings in Syniti DR/DBMoto. Syniti DR/DBMoto reads the number from the source and converts it to a string using the decimal separator defined on the system where Syniti DR/DBMoto is installed. The value is passed as parameter (in string format) to the target database. If the target database has a decimal separator that does not match the one set up on the Syniti DR/DBMoto server, it throws an exception because it is not able to convert the number.
In some other cases, the database converts the number but with an incorrect precision/scale, so 5,37 may be replicated as 537.
This issue is caused by the different decimal separators set on the systems involved.
To solve the problem:
- Create a file called ExecuteList.xml.
- Edit the file to add the following information:
<connections xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.hitsw.com">
<execute> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';</execute>
Note that the separator should match the separator expected in the target database--in this case, it is a comma because the Oracle target uses a comma as decimal separator. Replace "MyOracleConnection" with the connection name as defined in the Management Center.
- Stop all DBMoto services (Server Agent and Data Replicator.)
- Copy the ExecuteList.xml under the Syniti DR/DBMoto installation folder.
- Restart the Syniti DR/DBMoto services.