You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by Gautam Deb <ga...@in2m.com> on 2007/06/12 11:48:13 UTC
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
Posted by Adrian Crum <ad...@hlmksw.com>.
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
>
Re: Problem - OFBiz startup with Oracle
Posted by Gautam Deb <ga...@in2m.com>.
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,
>
> 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
Posted by Jacopo Cappellato <ti...@sastau.it>.
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
Posted by Gautam Deb <ga...@in2m.com>.
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