View Issue Details

IDProjectCategoryView StatusLast Update
0003375SymmetricDSBugpublic2018-03-28 18:16
ReporteralbpAssigned Tojosh-a-hicks 
Priorityurgent 
Status closedResolutionfixed 
Product Version3.8.30 
Target Version3.9.6Fixed in Version3.9.6 
Summary0003375: Data Capture Can't Handle Large XMLTYPE Values
DescriptionXMLTYPE 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 Reproduce1. 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 InformationReproduced 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.

Activities

albp

2018-02-09 00:37

reporter   ~0001124

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.

OracleTriggerTemplate.java (25,676 bytes)
OracleDmlStatement.java (3,919 bytes)
OracleDdlReader.java (19,865 bytes)

albp

2018-03-13 23:11

reporter   ~0001149

Pull request raised for this issue https://github.com/JumpMind/symmetric-ds/pull/70

elong

2018-03-14 20:33

developer   ~0001151

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.

Related Changesets

SymmetricDS: 3.9 c27ccc22

2018-03-14 20:31:29

albp


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!

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

Issue History

Date Modified Username Field Change
2018-01-17 04:27 albp New Issue
2018-02-09 00:37 albp File Added: OracleTriggerTemplate.java
2018-02-09 00:37 albp File Added: OracleDmlStatement.java
2018-02-09 00:37 albp File Added: OracleDdlReader.java
2018-02-09 00:37 albp Note Added: 0001124
2018-02-13 11:12 elong Assigned To => josh-a-hicks
2018-02-13 11:12 elong Status new => assigned
2018-03-13 23:11 albp Note Added: 0001149
2018-03-14 20:33 elong Note Added: 0001151
2018-03-14 21:00 elong Changeset attached => SymmetricDS 3.9 c27ccc22
2018-03-23 08:08 admin Status assigned => resolved
2018-03-23 08:08 admin Resolution open => fixed
2018-03-23 08:08 admin Fixed in Version => 3.9.6
2018-03-23 08:08 admin Target Version => 3.9.6
2018-03-28 18:16 mmichalek Status resolved => closed