View Issue Details

IDProjectCategoryView StatusLast Update
0001203SymmetricDSBugpublic2014-02-03 01:53
Reporterdocal Assigned Tochenson  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.4.2 
Target Version3.5.0Fixed in Version3.5.0 
Summary0001203: SymmetricDS cannot operate on Oracle tables with dollar sign in name
DescriptionThe problem seems to be in org.jumpmind.db.platform.AbstractDatabasePlatform.readTableFromDatabase() that cannot reach Oracle table with dollar sign ($) in name. Due to this issue SymmetricDS can neither track content of such tables with error "Could not find any database tables matching 'SCHEMA.TABLE$NAME' in the datasource that is configured", nor export data from such table via bin/dbexport command with NullPointerException (see Additional Information).


Steps To Reproduce1. Connect to Oracle instance (I used Oracle Database 11g Release 11.2.0.1.0 with latest Oracle thin JDBC driver 11.2.0.3)
2. Create a table with dollar sign in name, e.g. A$B
3a. Try to export data from this table via dbexport command:
    ../bin/dbexport --sql 'select * from A$B' 'A$B'
You'll get NullPointerException (see Additional Information).

3b. Alternatively, export data without --sql option:
You'll get RuntimeException (see Additional Information).

The problem seems to be Oracle-specific, e.g. same scenario works fine with MySQL
Additional Informationjava.lang.NullPointerException
        at org.jumpmind.symmetric.io.data.DbExport.exportTables(DbExport.java:165)
        at org.jumpmind.symmetric.io.data.DbExport.exportTable(DbExport.java:153)
        at org.jumpmind.symmetric.DbExportCommand.executeWithOptions(DbExportCommand.java:177)
        at org.jumpmind.symmetric.AbstractCommandLauncher.execute(AbstractCommandLauncher.java:131)
        at org.jumpmind.symmetric.DbExportCommand.main(DbExportCommand.java:72)



java.lang.RuntimeException: Cannot find table A$B in catalog null and schema YOUR_SCHEMA
        at org.jumpmind.symmetric.io.data.DbExport.exportTables(DbExport.java:143)
        at org.jumpmind.symmetric.DbExportCommand.executeWithOptions(DbExportCommand.java:181)
        at org.jumpmind.symmetric.AbstractCommandLauncher.execute(AbstractCommandLauncher.java:131)
        at org.jumpmind.symmetric.DbExportCommand.main(DbExportCommand.java:72)
TagsNo tags attached.

Activities

docal

2013-05-07 14:23

reporter   ~0000251

I was able to workaround this issue by defining SYNONYM without dollar sign in name for the table in question and using this SYNONYM instead, thanks to recent 0001061 improvement.
NB that you might need to adjust db.connection.properties for your Oracle connection by adding includeSynonyms=true as suggested in conf/symmetric.properties.

docal

2013-06-10 12:23

reporter   ~0000274

Since I cannot comment on 0001061, I'll have to point out here that support for Oracle synonyms is not complete. Oracle synonyms apparently doesn't expose any primary key, regardless of original table structure. Lack of primary key leads to use of full row as a key, thus rendering updates impossible. One can explicitly define primary key for the synonym via SYNC_KEY_NAMES column of SYM_TRIGGER to overcome this issue.

docal

2013-06-10 12:28

reporter   ~0000275

The cause of this issue is in org.jumpmind.db.platform.oracle.OracleDdlReader.readTable() method where it checks, whether tableName.indexOf('$') > 0 and returns null instead of the table assuming the table is system and shouldn't be read.

chenson

2013-07-22 21:52

administrator   ~0000325

prepare for release

Related Changesets

SymmetricDS: master aa99eaf9

2013-06-12 17:25:54

chenson

Details Diff
0001203: SymmetricDS cannot operate on Oracle tables with dollar sign in name Affected Issues
0001203
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlReader.java Diff File

SymmetricDS: master 3040e42a

2013-06-12 17:30:38

chenson

Details Diff
0001203: SymmetricDS cannot operate on Oracle tables with dollar sign in name Affected Issues
0001203
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlReader.java Diff File

Issue History

Date Modified Username Field Change
2013-05-07 10:38 docal New Issue
2013-05-07 14:23 docal Note Added: 0000251
2013-06-08 15:05 chenson Target Version => 3.6.0
2013-06-10 12:23 docal Note Added: 0000274
2013-06-10 12:28 docal Note Added: 0000275
2013-06-10 18:56 chenson Target Version 3.6.0 => 3.5.0
2013-06-12 21:26 chenson Status new => resolved
2013-06-12 21:26 chenson Fixed in Version => 3.5.0
2013-06-12 21:26 chenson Resolution open => fixed
2013-06-12 21:26 chenson Assigned To => chenson
2013-07-22 21:52 chenson Note Added: 0000325
2013-07-22 21:52 chenson Status resolved => closed
2014-02-03 01:53 Changeset attached => SymmetricDS trunk r7384
2014-02-03 01:53 Changeset attached => SymmetricDS trunk r7385
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master 3040e42a
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master aa99eaf9