You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by "seba.wagner@gmail.com" <se...@gmail.com> on 2011/11/02 09:45:45 UTC
Re: Problems with Oracle 11g and primary_key as rowtype IDENTITY
Thanks Miłosz,
I will try it out and come back with the results!
Thanks
Sebastian
2011/10/25 Miłosz Tylenda <mt...@o2.pl>
> Sebastian,
>
> For the sequence name too long problem, you could try setting the
> maxAutoAssignNameLength dictionary property to the maximum sequence name
> length in Oracle.
>
> You can also set the sequence name via the autoAssignSequenceName
> dictionary property but in this case you will rather need to create the
> sequence manually.
>
> If the above fails, there is an option to override
> DBDictionary.getGeneratedKeySequenceName or
> OracleDictionary.getCreateTableSQL methods.
>
> You might want to open a JIRA [1] issue for this problem.
>
> Let us know if this helps.
>
> Cheers,
> Milosz
>
> [1] https://issues.apache.org/jira/browse/OPENJPA
>
>
> > Or let me re-phrase my question:
> >
> > Is there a possibility to define an alternative name for the sequence
> > or a table when you are using the option:
> > <property name="openjpa.jdbc.DBDictionary"
> > value="oracle(UseTriggersForAutoAssign=true,BatchLimit=100)" />
> >
> > Or is it really required at this point to rename the table's primary key
> ??
> >
> > The error message Oracle throws is like that: "could not create
> > sequence because "appointmentcategory_seq" is too long name."
> >
> > I will also cross post this question to the Oracle support forums,
> > maybe they also have an idea how to workaround that.
> >
> > Btw: Using
> > <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema" />
> >
> > Also throws some error in our application using Oracle database...
> > So I always need to manually delete all tables and re-run the
> > application so that the tables are created (including sequences). Is
> > there a way of forcing openJPA to drop all tables? In hibernate you
> > would say "create" instead of "update". In openJPA I don't see any
> > alternative to "buildSchema". Is there a "createSchema" ?!
> > The documentation does not say so much about schema update /
> > forwarding.I don't see any other option that "buildSchema" mentioned
> > in the docs...
> >
> > Thanks
> > Sebastian
> >
> >
> >
> > 2011/10/22 seba.wagner@gmail.com <se...@gmail.com>:
> > > Hi,
> > >
> > > we are still trying to figure out what is going wrong with Oracle and
> openJPA.
> > >
> > > Our configuration now looks like that:
> > > <properties>
> > > <property
> name="openjpa.RuntimeUnenhancedClasses" value="unsupported"/>
> > > <property
> name="openjpa.ConnectionDriverName"
> > > value="oracle.jdbc.driver.OracleDriver"/>
> > > <property name="openjpa.ConnectionProperties"
> > > value="username=SYSTEM, password=admin"/>
> > > <property name="openjpa.ConnectionURL"
> > > value="jdbc:oracle:thin:@localhost:1521:openmeetings"/>
> > > <property
> name="openjpa.jdbc.SynchronizeMappings" value="buildSchema"/>
> > > <property name="openjpa.Log"
> value="DefaultLevel=WARN, Tool=INFO" />
> > > <property name="openjpa.DataCache"
> value="true"/>
> > > <property name="openjpa.QueryCache"
> value="true"/>
> > > <property
> name="openjpa.jdbc.DBDictionary"
> > > value="oracle(UseTriggersForAutoAssign=true,BatchLimit=100)" />
> > > <property
> name="openjpa.jdbc.QuerySQLCache" value="false"/>
> > > </properties>
> > >
> > > UseTriggersForAutoAssign=true is the magic key to use sequences as it
> seems ...
> > > however ...
> > >
> > > => The problem now is that Oracle complains that some sequences have a
> > > too long column name. How can we bypass that?
> > >
> > >
> > > Thanks
> > > Sebastian
> > >
> > >
> > >
> > > 2011/10/2 seba.wagner@gmail.com <se...@gmail.com>:
> > >> The guys from Oracle do (a bit offensive) argue that for such reasons
> > >> sequences do exist.
> > >>
> > >>
> https://forums.oracle.com/forums/thread.jspa?messageID=9909360#9909360
> > >>
> > >> Quote:
> > >> "Oracle has sequences for this purpose.
> > >> They are either directly used in an INSERT statement, or assigned to a
> > >> column by means of a trigger.."
> > >>
> > >> So my question might be: Why does openJPA not create sequences? Am I
> missing
> > >> some param in my config?
> > >> I have seen for MySQL there is an additional "sequence-table" created
> with
> > >> all index. In Oracle there seems to be no such table ... while except
> the
> > >> persistance.xml the code is 1:1 the same.
> > >>
> > >> Thanks,
> > >> Sebastian
> > >>
> > >> 2011/10/1 seba.wagner@gmail.com <se...@gmail.com>
> > >>>
> > >>> Hi,
> > >>>
> > >>> when using Oracle 11g with openJPA I do get the following warning
> while
> > >>> the tables are populated by openJPA:
> > >>>
> > >>> "Oracle" does not support auto-assigne d column values. The column
> may
> > >>> not behave as desired.
> > >>>
> > >>> ... and the effect is obviously that, as soon as you try to add a new
> > >>> column with a null value for a primary key, Oracle will throw an
> > >>> Exception...
> > >>>
> > >>> I can read from the Issue tracker:
> > >>> https://issues.apache.org/jira/browse/OPENJPA-1090
> > >>>
> > >>> But actually the JIRA Issue 1090 is about adding this warning, not
> about
> > >>> how to solve it.
> > >>>
> > >>> What is actually the solution to fix it?
> > >>> Is it database related or do we need to change our code?
> > >>>
> > >>> Thank you!
> > >>> Sebastian
> > >>>
> > >>> --
> > >>> Sebastian Wagner
> > >>> http://www.openmeetings.de
> > >>> http://www.webbase-design.de
> > >>> http://www.wagner-sebastian.com
> > >>> seba.wagner@gmail.com
> > >>
> > >>
> > >>
> > >> --
> > >> Sebastian Wagner
> > >> http://www.openmeetings.de
> > >> http://www.webbase-design.de
> > >> http://www.wagner-sebastian.com
> > >> seba.wagner@gmail.com
> > >>
> > >
> > >
> > >
> > > --
> > > Sebastian Wagner
> > > http://www.openmeetings.de
> > > http://www.webbase-design.de
> > > http://www.wagner-sebastian.com
> > > seba.wagner@gmail.com
> > >
> >
> >
> >
> >
>
--
Sebastian Wagner
http://www.openmeetings.de
http://www.webbase-design.de
http://www.wagner-sebastian.com
seba.wagner@gmail.com
Re: Problems with Oracle 11g and primary_key as rowtype IDENTITY
Posted by Rick Curtis <cu...@gmail.com>.
Thanks for sharing the documentation, one comment for you though... Please
reconsider listing RuntimeUnenhancedClasses as a recommended configuration.
We don't recommend using that feature as it has numerous problems.
Thanks,
Rick
On Thu, Dec 1, 2011 at 3:19 AM, seba.wagner@gmail.com <seba.wagner@gmail.com
> wrote:
> We finally where able to use Oracle, we've put some docs online howto
> configure JPA in our Spring enabled configuration, maybe useful for
> others:
> http://code.google.com/p/openmeetings/wiki/Configuration_Oracle
>
> Sebastian
>
> 2011/11/2 seba.wagner@gmail.com <se...@gmail.com>:
> > Thanks Miłosz,
> >
> > I will try it out and come back with the results!
> >
> > Thanks
> > Sebastian
> >
> >
> > 2011/10/25 Miłosz Tylenda <mt...@o2.pl>
> >>
> >> Sebastian,
> >>
> >> For the sequence name too long problem, you could try setting the
> >> maxAutoAssignNameLength dictionary property to the maximum sequence name
> >> length in Oracle.
> >>
> >> You can also set the sequence name via the autoAssignSequenceName
> >> dictionary property but in this case you will rather need to create the
> >> sequence manually.
> >>
> >> If the above fails, there is an option to override
> >> DBDictionary.getGeneratedKeySequenceName or
> >> OracleDictionary.getCreateTableSQL methods.
> >>
> >> You might want to open a JIRA [1] issue for this problem.
> >>
> >> Let us know if this helps.
> >>
> >> Cheers,
> >> Milosz
> >>
> >> [1] https://issues.apache.org/jira/browse/OPENJPA
> >>
> >>
> >> > Or let me re-phrase my question:
> >> >
> >> > Is there a possibility to define an alternative name for the sequence
> >> > or a table when you are using the option:
> >> > <property name="openjpa.jdbc.DBDictionary"
> >> > value="oracle(UseTriggersForAutoAssign=true,BatchLimit=100)" />
> >> >
> >> > Or is it really required at this point to rename the table's primary
> key
> >> > ??
> >> >
> >> > The error message Oracle throws is like that: "could not create
> >> > sequence because "appointmentcategory_seq" is too long name."
> >> >
> >> > I will also cross post this question to the Oracle support forums,
> >> > maybe they also have an idea how to workaround that.
> >> >
> >> > Btw: Using
> >> > <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema"
> />
> >> >
> >> > Also throws some error in our application using Oracle database...
> >> > So I always need to manually delete all tables and re-run the
> >> > application so that the tables are created (including sequences). Is
> >> > there a way of forcing openJPA to drop all tables? In hibernate you
> >> > would say "create" instead of "update". In openJPA I don't see any
> >> > alternative to "buildSchema". Is there a "createSchema" ?!
> >> > The documentation does not say so much about schema update /
> >> > forwarding.I don't see any other option that "buildSchema" mentioned
> >> > in the docs...
> >> >
> >> > Thanks
> >> > Sebastian
> >> >
> >> >
> >> >
> >> > 2011/10/22 seba.wagner@gmail.com <se...@gmail.com>:
> >> > > Hi,
> >> > >
> >> > > we are still trying to figure out what is going wrong with Oracle
> and
> >> > > openJPA.
> >> > >
> >> > > Our configuration now looks like that:
> >> > > <properties>
> >> > > <property
> >> > > name="openjpa.RuntimeUnenhancedClasses" value="unsupported"/>
> >> > > <property
> >> > > name="openjpa.ConnectionDriverName"
> >> > > value="oracle.jdbc.driver.OracleDriver"/>
> >> > > <property name="openjpa.ConnectionProperties"
> >> > > value="username=SYSTEM, password=admin"/>
> >> > > <property name="openjpa.ConnectionURL"
> >> > > value="jdbc:oracle:thin:@localhost:1521:openmeetings"/>
> >> > > <property
> >> > > name="openjpa.jdbc.SynchronizeMappings" value="buildSchema"/>
> >> > > <property name="openjpa.Log"
> >> > > value="DefaultLevel=WARN, Tool=INFO" />
> >> > > <property name="openjpa.DataCache"
> >> > > value="true"/>
> >> > > <property name="openjpa.QueryCache"
> >> > > value="true"/>
> >> > > <property
> >> > > name="openjpa.jdbc.DBDictionary"
> >> > > value="oracle(UseTriggersForAutoAssign=true,BatchLimit=100)" />
> >> > > <property
> >> > > name="openjpa.jdbc.QuerySQLCache" value="false"/>
> >> > > </properties>
> >> > >
> >> > > UseTriggersForAutoAssign=true is the magic key to use sequences as
> it
> >> > > seems ...
> >> > > however ...
> >> > >
> >> > > => The problem now is that Oracle complains that some sequences
> have a
> >> > > too long column name. How can we bypass that?
> >> > >
> >> > >
> >> > > Thanks
> >> > > Sebastian
> >> > >
> >> > >
> >> > >
> >> > > 2011/10/2 seba.wagner@gmail.com <se...@gmail.com>:
> >> > >> The guys from Oracle do (a bit offensive) argue that for such
> reasons
> >> > >> sequences do exist.
> >> > >>
> >> > >>
> >> > >>
> https://forums.oracle.com/forums/thread.jspa?messageID=9909360#9909360
> >> > >>
> >> > >> Quote:
> >> > >> "Oracle has sequences for this purpose.
> >> > >> They are either directly used in an INSERT statement, or assigned
> to
> >> > >> a
> >> > >> column by means of a trigger.."
> >> > >>
> >> > >> So my question might be: Why does openJPA not create sequences? Am
> I
> >> > >> missing
> >> > >> some param in my config?
> >> > >> I have seen for MySQL there is an additional "sequence-table"
> created
> >> > >> with
> >> > >> all index. In Oracle there seems to be no such table ... while
> except
> >> > >> the
> >> > >> persistance.xml the code is 1:1 the same.
> >> > >>
> >> > >> Thanks,
> >> > >> Sebastian
> >> > >>
> >> > >> 2011/10/1 seba.wagner@gmail.com <se...@gmail.com>
> >> > >>>
> >> > >>> Hi,
> >> > >>>
> >> > >>> when using Oracle 11g with openJPA I do get the following warning
> >> > >>> while
> >> > >>> the tables are populated by openJPA:
> >> > >>>
> >> > >>> "Oracle" does not support auto-assigne d column values. The
> column
> >> > >>> may
> >> > >>> not behave as desired.
> >> > >>>
> >> > >>> ... and the effect is obviously that, as soon as you try to add a
> >> > >>> new
> >> > >>> column with a null value for a primary key, Oracle will throw an
> >> > >>> Exception...
> >> > >>>
> >> > >>> I can read from the Issue tracker:
> >> > >>> https://issues.apache.org/jira/browse/OPENJPA-1090
> >> > >>>
> >> > >>> But actually the JIRA Issue 1090 is about adding this warning, not
> >> > >>> about
> >> > >>> how to solve it.
> >> > >>>
> >> > >>> What is actually the solution to fix it?
> >> > >>> Is it database related or do we need to change our code?
> >> > >>>
> >> > >>> Thank you!
> >> > >>> Sebastian
> >> > >>>
> >> > >>> --
> >> > >>> Sebastian Wagner
> >> > >>> http://www.openmeetings.de
> >> > >>> http://www.webbase-design.de
> >> > >>> http://www.wagner-sebastian.com
> >> > >>> seba.wagner@gmail.com
> >> > >>
> >> > >>
> >> > >>
> >> > >> --
> >> > >> Sebastian Wagner
> >> > >> http://www.openmeetings.de
> >> > >> http://www.webbase-design.de
> >> > >> http://www.wagner-sebastian.com
> >> > >> seba.wagner@gmail.com
> >> > >>
> >> > >
> >> > >
> >> > >
> >> > > --
> >> > > Sebastian Wagner
> >> > > http://www.openmeetings.de
> >> > > http://www.webbase-design.de
> >> > > http://www.wagner-sebastian.com
> >> > > seba.wagner@gmail.com
> >> > >
> >> >
> >> >
> >> >
> >> >
> >
> >
> >
> >
> > --
> > Sebastian Wagner
> > http://www.openmeetings.de
> > http://www.webbase-design.de
> > http://www.wagner-sebastian.com
> > seba.wagner@gmail.com
>
>
>
> --
> Sebastian Wagner
> http://www.openmeetings.de
> http://www.webbase-design.de
> http://www.wagner-sebastian.com
> seba.wagner@gmail.com
>
--
*Rick Curtis*
Re: Problems with Oracle 11g and primary_key as rowtype IDENTITY
Posted by "seba.wagner@gmail.com" <se...@gmail.com>.
We finally where able to use Oracle, we've put some docs online howto
configure JPA in our Spring enabled configuration, maybe useful for
others:
http://code.google.com/p/openmeetings/wiki/Configuration_Oracle
Sebastian
2011/11/2 seba.wagner@gmail.com <se...@gmail.com>:
> Thanks Miłosz,
>
> I will try it out and come back with the results!
>
> Thanks
> Sebastian
>
>
> 2011/10/25 Miłosz Tylenda <mt...@o2.pl>
>>
>> Sebastian,
>>
>> For the sequence name too long problem, you could try setting the
>> maxAutoAssignNameLength dictionary property to the maximum sequence name
>> length in Oracle.
>>
>> You can also set the sequence name via the autoAssignSequenceName
>> dictionary property but in this case you will rather need to create the
>> sequence manually.
>>
>> If the above fails, there is an option to override
>> DBDictionary.getGeneratedKeySequenceName or
>> OracleDictionary.getCreateTableSQL methods.
>>
>> You might want to open a JIRA [1] issue for this problem.
>>
>> Let us know if this helps.
>>
>> Cheers,
>> Milosz
>>
>> [1] https://issues.apache.org/jira/browse/OPENJPA
>>
>>
>> > Or let me re-phrase my question:
>> >
>> > Is there a possibility to define an alternative name for the sequence
>> > or a table when you are using the option:
>> > <property name="openjpa.jdbc.DBDictionary"
>> > value="oracle(UseTriggersForAutoAssign=true,BatchLimit=100)" />
>> >
>> > Or is it really required at this point to rename the table's primary key
>> > ??
>> >
>> > The error message Oracle throws is like that: "could not create
>> > sequence because "appointmentcategory_seq" is too long name."
>> >
>> > I will also cross post this question to the Oracle support forums,
>> > maybe they also have an idea how to workaround that.
>> >
>> > Btw: Using
>> > <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema" />
>> >
>> > Also throws some error in our application using Oracle database...
>> > So I always need to manually delete all tables and re-run the
>> > application so that the tables are created (including sequences). Is
>> > there a way of forcing openJPA to drop all tables? In hibernate you
>> > would say "create" instead of "update". In openJPA I don't see any
>> > alternative to "buildSchema". Is there a "createSchema" ?!
>> > The documentation does not say so much about schema update /
>> > forwarding.I don't see any other option that "buildSchema" mentioned
>> > in the docs...
>> >
>> > Thanks
>> > Sebastian
>> >
>> >
>> >
>> > 2011/10/22 seba.wagner@gmail.com <se...@gmail.com>:
>> > > Hi,
>> > >
>> > > we are still trying to figure out what is going wrong with Oracle and
>> > > openJPA.
>> > >
>> > > Our configuration now looks like that:
>> > > <properties>
>> > > <property
>> > > name="openjpa.RuntimeUnenhancedClasses" value="unsupported"/>
>> > > <property
>> > > name="openjpa.ConnectionDriverName"
>> > > value="oracle.jdbc.driver.OracleDriver"/>
>> > > <property name="openjpa.ConnectionProperties"
>> > > value="username=SYSTEM, password=admin"/>
>> > > <property name="openjpa.ConnectionURL"
>> > > value="jdbc:oracle:thin:@localhost:1521:openmeetings"/>
>> > > <property
>> > > name="openjpa.jdbc.SynchronizeMappings" value="buildSchema"/>
>> > > <property name="openjpa.Log"
>> > > value="DefaultLevel=WARN, Tool=INFO" />
>> > > <property name="openjpa.DataCache"
>> > > value="true"/>
>> > > <property name="openjpa.QueryCache"
>> > > value="true"/>
>> > > <property
>> > > name="openjpa.jdbc.DBDictionary"
>> > > value="oracle(UseTriggersForAutoAssign=true,BatchLimit=100)" />
>> > > <property
>> > > name="openjpa.jdbc.QuerySQLCache" value="false"/>
>> > > </properties>
>> > >
>> > > UseTriggersForAutoAssign=true is the magic key to use sequences as it
>> > > seems ...
>> > > however ...
>> > >
>> > > => The problem now is that Oracle complains that some sequences have a
>> > > too long column name. How can we bypass that?
>> > >
>> > >
>> > > Thanks
>> > > Sebastian
>> > >
>> > >
>> > >
>> > > 2011/10/2 seba.wagner@gmail.com <se...@gmail.com>:
>> > >> The guys from Oracle do (a bit offensive) argue that for such reasons
>> > >> sequences do exist.
>> > >>
>> > >>
>> > >> https://forums.oracle.com/forums/thread.jspa?messageID=9909360#9909360
>> > >>
>> > >> Quote:
>> > >> "Oracle has sequences for this purpose.
>> > >> They are either directly used in an INSERT statement, or assigned to
>> > >> a
>> > >> column by means of a trigger.."
>> > >>
>> > >> So my question might be: Why does openJPA not create sequences? Am I
>> > >> missing
>> > >> some param in my config?
>> > >> I have seen for MySQL there is an additional "sequence-table" created
>> > >> with
>> > >> all index. In Oracle there seems to be no such table ... while except
>> > >> the
>> > >> persistance.xml the code is 1:1 the same.
>> > >>
>> > >> Thanks,
>> > >> Sebastian
>> > >>
>> > >> 2011/10/1 seba.wagner@gmail.com <se...@gmail.com>
>> > >>>
>> > >>> Hi,
>> > >>>
>> > >>> when using Oracle 11g with openJPA I do get the following warning
>> > >>> while
>> > >>> the tables are populated by openJPA:
>> > >>>
>> > >>> "Oracle" does not support auto-assigne d column values. The column
>> > >>> may
>> > >>> not behave as desired.
>> > >>>
>> > >>> ... and the effect is obviously that, as soon as you try to add a
>> > >>> new
>> > >>> column with a null value for a primary key, Oracle will throw an
>> > >>> Exception...
>> > >>>
>> > >>> I can read from the Issue tracker:
>> > >>> https://issues.apache.org/jira/browse/OPENJPA-1090
>> > >>>
>> > >>> But actually the JIRA Issue 1090 is about adding this warning, not
>> > >>> about
>> > >>> how to solve it.
>> > >>>
>> > >>> What is actually the solution to fix it?
>> > >>> Is it database related or do we need to change our code?
>> > >>>
>> > >>> Thank you!
>> > >>> Sebastian
>> > >>>
>> > >>> --
>> > >>> Sebastian Wagner
>> > >>> http://www.openmeetings.de
>> > >>> http://www.webbase-design.de
>> > >>> http://www.wagner-sebastian.com
>> > >>> seba.wagner@gmail.com
>> > >>
>> > >>
>> > >>
>> > >> --
>> > >> Sebastian Wagner
>> > >> http://www.openmeetings.de
>> > >> http://www.webbase-design.de
>> > >> http://www.wagner-sebastian.com
>> > >> seba.wagner@gmail.com
>> > >>
>> > >
>> > >
>> > >
>> > > --
>> > > Sebastian Wagner
>> > > http://www.openmeetings.de
>> > > http://www.webbase-design.de
>> > > http://www.wagner-sebastian.com
>> > > seba.wagner@gmail.com
>> > >
>> >
>> >
>> >
>> >
>
>
>
>
> --
> Sebastian Wagner
> http://www.openmeetings.de
> http://www.webbase-design.de
> http://www.wagner-sebastian.com
> seba.wagner@gmail.com
--
Sebastian Wagner
http://www.openmeetings.de
http://www.webbase-design.de
http://www.wagner-sebastian.com
seba.wagner@gmail.com