Community
Participate
Working Groups
When I configure the employee example to use sequence generators defined as: <sequence-generator name="EMP_SEQ" sequence-name="EMP_SEQ" allocation-size="25" /> I get the following correct SQL to create the sequence: CREATE SEQUENCE EMP_SEQ INCREMENT BY 25 START WITH 25 When I change the definition to be: <sequence-generator name="EMP_SEQ" sequence-name="EMP_SEQ" allocation-size="25" initial-value="25" /> The generate SQL to create the sequence is: CREATE SEQUENCE EMP_SEQ INCREMENT BY 25 START WITH 49 If I change the initial-value to be 100 then the START WITH becomes 124. The SQKL generated during the schema-gen is also a little odd to me: [EL Fine]: DROP SEQUENCE EMP_SEQ [EL Fine]: SELECT EMP_SEQ.NEXTVAL FROM DUAL [EL Fine]: SELECT 1 FROM DUAL [EL Warning]: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100614-r7608): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist Error Code: 2289 Call: SELECT EMP_SEQ.NEXTVAL FROM DUAL Query: ValueReadQuery(sql="SELECT EMP_SEQ.NEXTVAL FROM DUAL") [EL Fine]: CREATE SEQUENCE EMP_SEQ INCREMENT BY 25 START WITH 124 The commands are: 1. DROP SEQUENCE 2. SELECT dropped sequence 3. On failure do SELECT 1 from DUAL to verify we are connected 4. CREATE SEQUENCE
OK. I see my issue (30 seconds later). We add the start-with value to the initial-value and subtract 1 so that the first time we ask for a value we can start issuing sequence numbers on the user provided initial-value. The generated SQL is still confusing me though
I'm running right up against this using EclipseLink. Is there a work around? Some set of values for allocation-size and initial-value that works?
I have found that the SQL that generates the sequences sets the initial value to be the starts-with + increment - 1. This is done so that the first time a sequence is retrieved it can start assigning values starting with the initial-value you have specified. Although I found it confusing I believe it creates and uses the sequence so that your JPA configured values can be honoured.
closing as RESOLVED - WORKSFORME
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
