View Issue Details

IDProjectCategoryView StatusLast Update
0003522SymmetricDSBugpublic2019-03-14 08:48
Reporterjubi74Assigned Topmarzullo 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.8.26 
Target Version3.10.0Fixed in Version3.10.0 
Summary0003522: Table creation Error while initial Load into oracle DB (initial.load.create.first=true) when using Default value SYS_GUID()
DescriptionWhen creating table through SymmetricDS with column
ID RAW(20) Default SYS_GUID() NOT NULL
the Default value is quoted 'SYS_GUID()'.
Need to remove the quotes for valid table creation with oracle database 11g.

Sample Log:
2018-04-18 15:19:00,043 INFO [mpos] [DefaultDatabaseWriter] [mpos-dataloader-2] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor" schema="DB">
    <table name="CASHMANVOUCHER">
        <column name="ID" primaryKey="true" required="true" type="VARBINARY" size="20" default="SYS_GUID()">
            <platform-column name="oracle" type="RAW" size="20" default="SYS_GUID()"/>
        </column>
        <column name="CSHORTTEXT" type="VARCHAR" size="42">
            <platform-column name="oracle" type="VARCHAR2" size="42"/>
        </column>
        <column name="LVOUCHER" type="DECIMAL" size="11">
            <platform-column name="oracle" type="NUMBER" size="11"/>
        </column>
    </table>
</database>
2018-04-18 15:19:00,043 INFO [mpos] [OracleDatabasePlatform] [mpos-dataloader-2] Running alter sql:
CREATE TABLE "DB"."CASHMANVOUCHER"(
    "ID" RAW(20) DEFAULT 'SYS_GUID()' NOT NULL,
    "CSHORTTEXT" VARCHAR2(42),
    "LVOUCHER" NUMBER(11)
);
ALTER TABLE "DB"."CASHMANVOUCHER"
    ADD CONSTRAINT "CASHMANVOUCHER_PK" PRIMARY KEY ("ID");

2018-04-18 15:19:00,058 WARN [mpos] [JdbcSqlTemplate] [mpos-dataloader-2] ORA-01465: invalid hex number
. Failed to execute: CREATE TABLE "DB"."CASHMANVOUCHER"(
    "ID" RAW(20) DEFAULT 'SYS_GUID()' NOT NULL,
    "CSHORTTEXT" VARCHAR2(42),
    "LVOUCHER" NUMBER(11)
)
2018-04-18 15:19:00,058 ERROR [mpos] [DefaultDatabaseWriter] [mpos-dataloader-2] Failed to alter table using the following xml: <?xml version="1.0"?>

Steps To Reproduceinitial load with create first
using any table including column with Default value SYS_GUID()
Additional Informationworks with change in
org.jumpmind.db.platform.AbstractDdlBuilder.java

...
boolean shouldUseQuotes = !isNull && !TypeMap.isNumericType(typeCode) &&
                !(TypeMap.isDateTimeType(typeCode)
                        && (defaultValueStr.toUpperCase().startsWith("TO_DATE(")
                                || defaultValueStr.toUpperCase().startsWith("SYSDATE")
                                || defaultValueStr.toUpperCase().startsWith("SYSTIMESTAMP")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT_TIMESTAMP")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT_TIME")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT_DATE")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT TIMESTAMP")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT TIME")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT DATE")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT_USER")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT USER")
                                || defaultValueStr.toUpperCase().startsWith("USER")
                                || defaultValueStr.toUpperCase().startsWith("SYSTEM_USER")
                                || defaultValueStr.toUpperCase().startsWith("SESSION_USER")
                                || defaultValueStr.toUpperCase().startsWith("DATE '")
                                || defaultValueStr.toUpperCase().startsWith("TIME '")
                                || defaultValueStr.toUpperCase().startsWith("TIMESTAMP '")
                                || defaultValueStr.toUpperCase().startsWith("INTERVAL '")
                                )) &&
                !(defaultValueStr.toUpperCase().startsWith("N'") && defaultValueStr.endsWith("'")) &&
                !(defaultValueStr.toUpperCase().startsWith("SYS_GUID()"));
TagsNo tags attached.

Activities

hanes

2018-10-17 09:46

developer   ~0001275

Issue still exists in 3.9.14. The issue seems to be that SymmetricDS assumes the default value is a constant. In the case of functions, that's not the case.

pmarzullo

2019-01-25 08:43

developer   ~0001388

SYS_GUID Is an Oracle specific function.
There now is an implementation of the printDefaultValue in the OracleDdlBuilder class.
This class can be enhanced as needed when other functions need to be outside of single quotes.

Related Changesets

SymmetricDS: 3.10 04b2fb17

2019-01-25 08:40:17

Philip Marzullo

Details Diff
0003522: Table creation Error while initial Load into oracle DB
(initial.load.create.first=true) when using Default value SYS_GUID()

0003522
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlBuilder.java Diff File

SymmetricDS: 3.10 b6d9e04a

2019-01-25 09:26:04

Philip Marzullo

Details Diff
0003522: Table creation Error while initial Load into oracle DB
(initial.load.create.first=true) when using Default value SYS_GUID()

0003522
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2018-04-19 11:25 jubi74 New Issue
2018-10-17 09:46 hanes Assigned To => hanes
2018-10-17 09:46 hanes Status new => confirmed
2018-10-17 09:46 hanes Note Added: 0001275
2018-10-17 09:46 hanes Assigned To hanes =>
2018-10-17 14:24 hanes Priority normal => low
2018-10-17 14:24 hanes Target Version => 3.10.0
2018-10-29 12:55 hanes Priority low => normal
2019-01-23 10:47 pmarzullo Assigned To => pmarzullo
2019-01-23 10:47 pmarzullo Status confirmed => assigned
2019-01-25 08:43 pmarzullo Status assigned => resolved
2019-01-25 08:43 pmarzullo Resolution open => fixed
2019-01-25 08:43 pmarzullo Fixed in Version => 3.10.0
2019-01-25 08:43 pmarzullo Note Added: 0001388
2019-01-25 09:00 Changeset attached => SymmetricDS 3.10 04b2fb17
2019-01-25 10:00 Changeset attached => SymmetricDS 3.10 b6d9e04a
2019-03-14 08:48 admin Status resolved => closed