Product: | Syniti Replicate (a.k.a Syniti Data Replication, DBMoto) |
Version: | All |
ID: | 1673 |
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 Replicate. Syniti Replicate reads the number from the source and converts it to a string using the decimal separator defined on the system where Syniti Replicate 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 Replicate 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:
<?xml version="1.0"?>
<connections xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.hitsw.com">
<connection name="MyOracleConnection">
<execute> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';</execute>
</connection>
</connections>
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 Syniti Replication Agent and the Syniti Server Agent.
- Copy the ExecuteList.xml under the Syniti Replicate installation folder.
- Start all Syniti Replicate services.