View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004812 | SymmetricDS | Improvement | public | 2021-02-05 07:11 | 2025-03-26 17:33 |
| Reporter | kraynopp | Assigned To | |||
| Priority | normal | ||||
| Status | closed | Resolution | no change required | ||
| Product Version | 3.12.6 | ||||
| Summary | 0004812: NULL and empty string should be considered as equal for Oracle | ||||
| Description | Oracle consider empty string as null. It is impossible to insert empty string into varchar2 field, it will be replaced by null. See here: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Nulls.html "Oracle Database treats a character value with a length of zero as null" But other rdbms can insert empty string in text filelds. It can leads to mistakes during conflict resolution. | ||||
| Steps To Reproduce | Source database is posgres, destination - oracle. Conflict resolution is configured. From oracle to postgres - FALLBACK. From postgres to oracle - IGNORE. 1. Record updates in postgres. New value of text field is empty string now. SymmetricDS in oracle accept this value, but rdbms converts it to NULL. 2. The same record updates or deletes in postgres. SymmetricDS in oracle compares old value of text field from postgres (empty string) and value of varchar2 field (null). Empty string is not equal to null, conflict detected, update or delete is rejected. Table becomes out of sync. | ||||
| Tags | dialect: oracle | ||||
|
|
https://forums.oracle.com/ords/apexds/post/null-and-empty-string-1971 Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls. |