Psuedo Guru Blog by a wannabe guru with spelling issues.

Table/View 'SEQUENCE' does not exist

While experimenting with the glassfish JPA implementation (toplink essentials), I ran into the following problem while trying to persist an entity with @GeneratedValue specified for an id:

Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b11-fcs (12/04/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Table/View 'SEQUENCE' does not exist.
Error Code: 20000
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
 bind => [50, SEQ_GEN]
Query: DataModifyQuery()
 at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
 at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566)
 at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452)
 at oracle.toplink.essentials.internal.sessions.AbstractSession.executeCall(AbstractSession.java:690)
...yada yada yada

Turns out that once you set an ID to autogenerate, toplink requires that you have a "sequence" table created, and it does not create it automatically. Found that little tidbit here.

Here is the SQL you need to run against your database to make the problem go away:

-- needed for autogenerated primary keys
CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50), SEQ_COUNT DECIMAL(15));
INSERT INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) values ('SEQ_GEN', 0);