Problem - OFBiz startup with Oracle
Locked
Problem - OFBiz startup with Oracle
 
|
I am trying to start OFBiz with Oracle 10g as back-end
database. While startup Oracle throws an error -
2007-06-12 12:53:08,056 (main) [DatabaseUtil.java:checkDb:364 :ERROR] :: Could not create table [s2.AGREEMENT_PROMO_APPL]: SQL Exception while executing the following: CREATE TABLE s2.AGREEMENT_PROMO_APPL (AGREEMENT_ID VARCHAR2(20) NOT NULL, AGREEMENT_ITEM_SEQ_ID VARCHAR2(20) NOT NULL, PRODUCT_PROMO_ID VARCHAR2(20) NOT NULL, FROM_DATE TIMESTAMP WITH TIME ZONE NOT NULL, THRU_DATE TIMESTAMP WITH TIME ZONE, SEQUENCE_NUM NUMBER(18,0), LAST_UPDATED_STAMP TIMESTAMP WITH TIME ZONE, LAST_UPDATED_TX_STAMP TIMESTAMP WITH TIME ZONE, CREATED_STAMP TIMESTAMP WITH TIME ZONE, CREATED_TX_STAMP TIMESTAMP WITH TIME ZONE, CONSTRAINT PK_AGREEMENT_PROMO_APPL PRIMARY KEY (AGREEMENT_ID, AGREEMENT_ITEM_SEQ_ID, PRODUCT_PROMO_ID, FROM_DATE)) Error was: java.sql.SQLException: ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key This is happening for other entities also which have a timestamp field as part of their keys. Any help would be appreciated. Regards, Gautam Deb |
Re: Problem - OFBiz startup with Oracle
|
|
To add on that -
I have modified the fieldtypeoracle.xml file to change the mapping for "date-time" as <field-type-def type="date-time" sql-type="TIMESTAMP WITH TIME ZONE" java-type="java.sql.Timestamp"></field-type-def> This is required as we are migrating our existing data (from PostgreSQL) which contains many of the fields as TIMESTAMPTZ. Also if anybody can suggest a way to get rid of the timezone part while migrating to Oracle (of-course maintaining the accuracy in date). Thanks.. Gautam Gautam Deb wrote: I am trying to start OFBiz with Oracle 10g as back-end database. While startup Oracle throws an error - |
Re: Problem - OFBiz startup with Oracle
|
|
Gautam,
did you get the same error before changing the field type to "TIMESTAMP WITH TIME ZONE" ? Jacopo Gautam Deb wrote: > To add on that - > > I have modified the fieldtypeoracle.xml file to change the mapping for > "date-time" as > <field-type-def type="date-time" sql-type="TIMESTAMP WITH TIME ZONE" > java-type="java.sql.Timestamp"></field-type-def> > > This is required as we are migrating our existing data (from PostgreSQL) > which contains many of the fields as TIMESTAMPTZ. > > Also if anybody can suggest a way to get rid of the timezone part while > migrating to Oracle (of-course maintaining the accuracy in date). > > Thanks.. > Gautam > > > > Gautam Deb wrote: >> I am trying to start OFBiz with Oracle 10g as back-end database. While >> startup Oracle throws an error - >> >> 2007-06-12 12:53:08,056 (main) [DatabaseUtil.java:checkDb:364 :ERROR] >> :: Could not create table [s2.AGREEMENT_PROMO_APPL]: SQL Exception >> while executing the following: >> CREATE TABLE s2.AGREEMENT_PROMO_APPL (AGREEMENT_ID VARCHAR2(20) NOT >> NULL, AGREEMENT_ITEM_SEQ_ID VARCHAR2(20) NOT NULL, PRODUCT_PROMO_ID >> VARCHAR2(20) NOT NULL, FROM_DATE TIMESTAMP WITH TIME ZONE NOT NULL, >> THRU_DATE TIMESTAMP WITH TIME ZONE, SEQUENCE_NUM NUMBER(18,0), >> LAST_UPDATED_STAMP TIMESTAMP WITH TIME ZONE, LAST_UPDATED_TX_STAMP >> TIMESTAMP WITH TIME ZONE, CREATED_STAMP TIMESTAMP WITH TIME ZONE, >> CREATED_TX_STAMP TIMESTAMP WITH TIME ZONE, CONSTRAINT >> PK_AGREEMENT_PROMO_APPL PRIMARY KEY (AGREEMENT_ID, >> AGREEMENT_ITEM_SEQ_ID, PRODUCT_PROMO_ID, FROM_DATE)) >> >> Error was: java.sql.SQLException: ORA-02329: column of datatype >> TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key >> >> This is happening for other entities also which have a timestamp field >> as part of their keys. >> >> Any help would be appreciated. >> >> Regards, >> Gautam Deb |
Re: Problem - OFBiz startup with Oracle
|
|
Jacopo,
No, the error I mentioned is after the change, I just forgot to include that information as part of first email. Regards, Gautam Deb Jacopo Cappellato wrote: Gautam, |
Re: Problem - OFBiz startup with Oracle
|
|
In reply to this post by Gautam Deb
I'm not familiar with Oracle or PostgreSQL, but I know that a Timestamp data type remains constant
regardless of the time zone. In other words, the time zone portion of the data is optional. Maybe there is a function in PostgreSQL that will convert the field to a plain Timestamp. You could use that function to export the data to another table. Gautam Deb wrote: > To add on that - > > I have modified the fieldtypeoracle.xml file to change the mapping for > "date-time " as > <field-type-def type="date-time" sql-type="TIMESTAMP WITH TIME ZONE" > java-type="java.sql.Timestamp"></field-type-def> > > This is required as we are migrating our existing data (from PostgreSQL) > which contains many of the fields as TIMESTAMPTZ. > > Also if anybody can suggest a way to get rid of the timezone part while > migrating to Oracle (of-course maintaining the accuracy in date). > > Thanks.. > Gautam > > > > Gautam Deb wrote: > >> I am trying to start OFBiz with Oracle 10g as back-end database. While >> startup Oracle throws an error - >> >> 2007-06-12 12:53:08,056 (main) [DatabaseUtil.java:checkDb:364 :ERROR] >> :: Could not create table [s2.AGREEMENT_PROMO_APPL]: SQL Exception >> while executing the following: >> CREATE TABLE s2.AGREEMENT_PROMO_APPL (AGREEMENT_ID VARCHAR2(20) NOT >> NULL, AGREEMENT_ITEM_SEQ_ID VARCHAR2(20) NOT NULL, PRODUCT_PROMO_ID >> VARCHAR2(20) NOT NULL, FROM_DATE TIMESTAMP WITH TIME ZONE NOT NULL, >> THRU_DATE TIMESTAMP WITH TIME ZONE, SEQUENCE_NUM NUMBER(18,0), >> LAST_UPDATED_STAMP TIMESTAMP WITH TIME ZONE, LAST_UPDATED_TX_STAMP >> TIMESTAMP WITH TIME ZONE, CREATED_STAMP TIMESTAMP WITH TIME ZONE, >> CREATED_TX_STAMP TIMESTAMP WITH TIME ZONE, CONSTRAINT >> PK_AGREEMENT_PROMO_APPL PRIMARY KEY (AGREEMENT_ID, >> AGREEMENT_ITEM_SEQ_ID, PRODUCT_PROMO_ID, FROM_DATE)) >> >> Error was: java.sql.SQLException: ORA-02329: column of datatype >> TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key >> >> This is happening for other entities also which have a timestamp field >> as part of their keys. >> >> Any help would be appreciated. >> >> Regards, >> Gautam Deb > |
«
Return to OFBiz - Dev
|
1 view|%1 views
| Free forum by Nabble | Edit this page |