JPA: Oracle Sequence Generator not up to date

[Former Member]

Hi,

I'm using the JPA Oracle Sequence Generator in one of my JPA classes:

@Entity
@Table(name = "DACC_COST_TYPE")
public class JPACostType implements Serializable {
@SequenceGenerator(name = "CostTypeGenerator", sequenceName = "DACC_COST_TYPE_SEQ")
    @Column(name = "ID_COST_TYPE")
    @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CostTypeGenerator")
    private Integer idCostType;
   ...
}

In order to persist a new object I perform the following code:

@PersistenceContext
private EntityManager em;
...
JPACostType myJPA = new JPACostType();
myJPA.setIdCostType = null;
...
em.merge(myJPA);
em.flush();
...

Normally this works fine. But after deploying the app there sometimes happens an error:

Caused by: javax.persistence.PersistenceException: SQLException while inserting entity {com.karmann.dacc.ejb.busilog.jpa.JPACostType(idCostType=4)}.

at com.sap.engine.services.orpersistence.core.PersistenceContextImpl.flush(PersistenceContextImpl.java:278)

at com.sap.engine.services.orpersistence.core.PersistenceContextImpl.beforeCompletion(PersistenceContextImpl.java:565)

at com.sap.engine.services.orpersistence.entitymanager.EntityManagerImpl.beforeCompletion(EntityManagerImpl.java:410)

at com.sap.engine.services.orpersistence.environment.AppJTAEnvironmentManager.beforeCompletion(AppJTAEnvironmentManager.java:197)

at com.sap.engine.services.ts.jta.impl.TransactionImpl.commit(TransactionImpl.java:232)

... 52 more

Caused by: java.sql.SQLException: ORA-00001: unique constraint (AEMA.DACC_COST_TYPE_PK) violated

Obviously JPA does not fetch the new key by accessing the Oracle sequence. This documents "next value = 5". Does JPA fetch the new key from its cache? Is there any possibility to avoid this?

Thanks for each hint,

Christoph

[former_member617985 · Accepted solution]

additional remark:

the allocationSize must fit with the sequence on the DB, jpa default value is 50

pls try to annotate allocationSize=1 if you created your sequence without increment

regards

-Andreas

[Sharathmg]

Hello Christoph Schäfer ,

I am stuck with a similar issue. I was able to save mutiple entries and there has not been much change to my JPA. I added new entities and new sequences.

Now, I get the error Caused by: javax.persistence.PersistenceException: java.sql.SQLException: ORA-02289: Sequence ist nicht vorhanden.

I have checked the name of sequence and sequence next val on the DB. It works on DB but when i execute it from ejb, it gives me thsi error. Now, it gives the error for all previously working JPA entities.

I have also provided allocationSize = 1 for all entities.

Please let me know, possible cause/solution to this issue.

thank you.

Regards,

Sharath

[Former Member]

Ok, now I've got it. The solution was in fact "allocationSize=1".

[Former Member]

In case of error SQL-Trace in fact reports that there is no query against the Oracle-Sequence. In case of success I can see the query against the sequence.

Regards,

Christoph

[Former Member]

Hi Andreas,

is it true that the sql trace is only available for datasources of type "Open SQL"? My datasource is of type "Vendor SQL".

Regards,

Christoph

[Vlado]

Hi Christoph,

SQL Trace can be used with Open SQL and Native SQL, but not and Vendor SQL.

With Native SQL you also get another important feature compared to Vendor SQL - statement pooling.

For more information please check the bottom of [Overview of the Java Persistence Infrastructure|http://help.sap.com/saphelp_nwce10/helpdata/en/2d/23b32e8c0152469edf07909703fe03/frameset.htm].

Regards,

\-- Vladimir

[Former Member]

Hi Andreas,

thanks for your answers. "allocationSize=1" does not fix my problem. I will try to follow your other hints.

Regards,

Christoph

[former_member617985]

Hi Christoph,

this is somehow strange, I don't understand how the key 4 comes into the DB, could it be that more than one application is running and using the sequence ?

To see what's really going on we must have a look at a SqlTrace of such a problem, you can use the url /OpenSQLMonitors for that or search at help.sap.com for sqltrace to get some more info.

btw: why do you use merge instead of persist when the entity is newly created ?

regards

-Andreas