View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003375 | SymmetricDS | Bug | public | 2018-01-17 09:27 | 2018-03-28 22:16 |
| Reporter | albp | Assigned To | josh-a-hicks | ||
| Priority | urgent | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.8.30 | ||||
| Target Version | 3.9.6 | Fixed in Version | 3.9.6 | ||
| Summary | 0003375: Data Capture Can't Handle Large XMLTYPE Values | ||||
| Description | XMLTYPE data exceeding a certain threshold in size (probably 32K) fails to insert, update or delete with "ORA-19011: Character string buffer too small" on Oracle. The issue is caused by limitations with Oracle's REPLACE and TO_CLOB and XMLTYPEs found in SymmetricDS's trigger code. As a workaround, XMLTYPE method getClobVal could be used e.g. DECODE( dbms_lob.getlength( :new."XMLCOL".getclobval( ) ), NULL, TO_CLOB( '' ), '"' || REPLACE( REPLACE( :new."XMLCOL".getclobval( ), '\', '\\' ), '"', '\"' ) || '"' ) Although unconfirmed, I suspect you also have to wrap the string literals in TO_CLOB to ensure Oracle doesn't do an implicit type conversion to VARCHAR2. DECODE( dbms_lob.getlength( :new.x.getclobval( ) ), NULL, TO_CLOB( '' ), TO_CLOB('"') || REPLACE( REPLACE( :new.x.getclobval( ), '\', '\\' ), '"', '\"' ) || TO_CLOB('"') ) | ||||
| Steps To Reproduce | 1. Set triggers on a table with an XMLTYPE column to: - * DO NOT - Stream Lobs and * DO - Capture Lobs 2. Attempt to INSERT an XMLTYPE value that exceeds 32K. The INSERT should fail with ORA-19011: Character string buffer too small. | ||||
| Additional Information | Reproduced on both Oracle 11 and 12 however on Oracle 11, it's important the storage type of the XMLTYPE column is set to CLOB. If it's set to BINARY, you'll get a (spurious?) error ORA-03001: unimplemented feature. | ||||
| Tags | No tags attached. | ||||
|
|
See attached workaround / potential solution to the problem. I managed to get it working by altering 3 classes as follows noting, since reporting the issue, I found the initial load feature also failed with a similar issue: - 1. org.jumpmind.symmetric.db.oracle.OracleTriggerTemplate - added xmlColumnTemplate implementing the aforementioned getClobVal workaround. 2. org.jumpmind.db.platform.oracle.OracleDmlStatement - added XMLType support to appendColumnParameter and in getTypeCode, altered the mapping of XML(type) from Types.VARCHAR to Types.CLOB. 3. org.jumpmind.db.platform.oracle.OracleDdlReader - altered the mapping of XML(type) from Types.LONGVARCHAR to Types.SQLXML. |
|
|
Pull request raised for this issue https://github.com/JumpMind/symmetric-ds/pull/70 |
|
|
I reviewed code and accepted pull request onto 3.9. It follows similar pattern as geometry types, so it is a clean change. (Note to team, it would be nice if we had an easy way to add testing of new types to the unit testing framework.) Josh, please review and resolve issue when you're satisfied. |
|
SymmetricDS: 3.9 c27ccc22 2018-03-14 20:31:29 Committer: elong Details Diff |
0003375 Data Capture Can't Handle Large XMLTYPE Values (0000070) Looks good to me. I like that you followed same pattern as the geometry types. Thanks for testing and submitting! |
Affected Issues 0003375 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java | Diff File | ||
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDmlStatement.java | Diff File | ||
| mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlReader.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-01-17 09:27 | albp | New Issue | |
| 2018-02-09 05:37 | albp | File Added: OracleTriggerTemplate.java | |
| 2018-02-09 05:37 | albp | File Added: OracleDmlStatement.java | |
| 2018-02-09 05:37 | albp | File Added: OracleDdlReader.java | |
| 2018-02-09 05:37 | albp | Note Added: 0001124 | |
| 2018-02-13 16:12 | elong | Assigned To | => josh-a-hicks |
| 2018-02-13 16:12 | elong | Status | new => assigned |
| 2018-03-14 03:11 | albp | Note Added: 0001149 | |
| 2018-03-15 00:33 | elong | Note Added: 0001151 | |
| 2018-03-15 01:00 | elong | Changeset attached | => SymmetricDS 3.9 c27ccc22 |
| 2018-03-23 12:08 | admin | Status | assigned => resolved |
| 2018-03-23 12:08 | admin | Resolution | open => fixed |
| 2018-03-23 12:08 | admin | Fixed in Version | => 3.9.6 |
| 2018-03-23 12:08 | admin | Target Version | => 3.9.6 |
| 2018-03-28 22:16 | mmichalek | Status | resolved => closed |