Spring/Hibernate/Oracle: ORA-02289 Sequence Does Not Exist?

Question

Getting a java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist when trying to insert a new object into my Oracle table. The table does have a sequence that automatically increments upon each entry.

I've been stuck on this for a few hours and after following similar answers to this question and other articles, I'm still stuck.

My class:

import java.sql.Timestamp;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import org.springframework.stereotype.Component;

@Entity
@Table(name = "MY_SCHEMA.MY_TABLE")
@Component
public class SomeClass {
    @Id
    @SequenceGenerator(name = "MY_SEQ", sequenceName = "MY_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "MY_SEQ")
    @Column(name = "MY_ID")
    private Integer myId;

    @Column(name = "MY_TS")
    private Timestamp ts;

    @Column(name = "MY_PARAM")
    private String myParameters;

    @Column(name = "ANOTHER_TS")
    private Timestamp anotherTimestamp;

    // empty constructor and getters/setters

}

---

DAO for the class:

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.springframework.stereotype.Component;

import mypackage.mysubpackage.SomeClass;

@Component
public class SomeClassDAO {

    private Session currentSession;
    private Transaction currentTransaction;

    private static SessionFactory getSessionFactory() {
        Configuration configuration = new Configuration().configure();
        configuration.addAnnotatedClass(SomeClass.class);
        StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties());
        SessionFactory factory = configuration.buildSessionFactory(builder.build());
        return factory;
    }

    public Session openCurrentSession() {
        currentSession = getSessionFactory().openSession();
        return currentSession;
    }

    public Session openCurrentSessionWithTransaction() {
        currentSession = getSessionFactory().openSession();
        currentTransaction = currentSession.beginTransaction();
        return currentSession;
    }

    public void closeCurrentSession() {
        currentSession.close();
    }

    public void closeCurrentSessionWithTransaction() {
        currentTransaction.commit();
        currentSession.close();
    }

    public Session getCurrentSession() {
        return currentSession;
    }

    public void setCurrentSession(Session currentSession) {
        this.currentSession = currentSession;
    }

    // post
    public void insertNew() {
        SomeClass obj = new SomeClass();
        obj.setParameters("abc");
        getCurrentSession().save(obj);
    }

}

---

DDL snippet for the sequence:

begin
   if inserting then
      if :NEW."MY_ID" is null then
         select MY_SEQ.nextval into :NEW."MY_ID" from dual;
      end if;
   end if;
end;

---

hibernate.cfg.xml:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-5.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="connection.url">jdbc:oracle:thin:@servername.company.net:123:ABC</property>
        <property name="connection.driver_class">oracle.jdbc.OracleDriver</property>
        <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>
        <property name="connection.username">user</property>
        <property name="connection.password">pass</property>
        <property name="show_sql">true</property>


    </session-factory>

</hibernate-configuration>

---

mvc-dispatchet-servlet.xml snippet:

<context:component-scan base-package="mypackage.mysubpackage"></context:component-scan>
<mvc:annotation-driven/>
<context:annotation-config/>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@servername.company.net:123:ABC"/>
    <property name="username" value="user"/>
    <property name="password" value="pass"/>
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
      p:packagesToScan="mypackage.mysubpackage"
      p:dataSource-ref="dataSource">
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="generateDdl" value="true"/>
            <property name="showSql" value="true"/>
        </bean>
    </property>
</bean>

<bean id="transactionManger" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>

<tx:annotation-driven transaction-manager="transactionManger"/>

Answer 1

begin
   if inserting then
      if :NEW."MY_ID" is null then
         select MY_SEQ.nextval into :NEW."MY_ID" from dual;
      end if;
   end if;
end;

This looks to me like part of oracle trigger rather than actual oracle Sequence. Check if sequence is actually present with name "MY_SEQ" in your schema.

If you have the sequence in place with the current JPA annotations on your id column, you do not require the trigger. JPA itself can get the sequence next value without the trigger.

If you still want to continue using the trigger read here.