java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

Question

In this method I am getting error java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist I have gone through posts and I m not getting any Idea to fix it

public void saveOrUpdateProcessRun(ProcessRun argProcessRun) {
LOGGER.info(METHOD_START);
getHibernateTemplate().saveOrUpdate(argProcessRun);
LOGGER.info(METHOD_END);
}

This is Mapping I have XML Based Configurations Generator class is not Working(PROC_RUN_ID_SEQ)

    <?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.uhg.esbdb.common.beans">

    <class name="ProcessRun" table="PROC_RUN">
        <id name="processRunID" type="integer" column="PROC_RUN_ID">
            <generator class="sequence">
                <param name="sequence">PROC_RUN_ID_SEQ</param>
            </generator>
        </id>

        <property name="processID" type="integer" column="PROC_ID" />
        <property name="processRunStartDatetime" type="timestamp"
            column="PROC_RUN_STRT_DTTM" />
        <property name="processRunEndDatetime" type="timestamp"
            column="PROC_RUN_END_DTTM" />
        <property name="processRunStatusCode" type="integer" column="PROC_RUN_STS_CD" />
        <property name="createdByID" type="string" column="CRE_BY_ID" />
        <property name="createdDatetime" type="timestamp" column="CRE_DTTM" />
        <property name="modifiedByID" type="string" column="MOD_BY_ID" />
        <property name="modifiedDatetime" type="timestamp" column="MOD_DTTM" />
    </class>

    <class name="FileLoad" table="FL_LOAD">
        <id name="fileLoadID" type="integer" column="FL_LOAD_ID">
            <generator class="sequence">
                <param name="sequence">FL_LOAD_ID_SEQ</param>
            </generator>
        </id>
        <property name="dataFileName" type="string" column="DATA_FL_NM" />
        <property name="dataFileSizeByteNumber" type="integer"
            column="DATA_FL_SZ_BYTE_NBR" />
        <property name="fileLoadStatusCode" type="integer" column="FL_LOAD_STS_CD" />
        <property name="loadStartDatetime" type="timestamp" column="LOAD_STRT_DTM" />
        <property name="loadEndDatetime" type="timestamp" column="LOAD_END_DTM" />
        <property name="createdByID" type="string" column="CRE_BY_ID" />
        <property name="createdDatetime" type="timestamp" column="CRE_DTTM" />
        <property name="modifiedByID" type="string" column="MOD_BY_ID" />
        <property name="modifiedDatetime" type="timestamp" column="MOD_DTTM" />
        <many-to-one name="processRun" class="ProcessRun" column="PROC_RUN_ID" />
    </class>

    <class name="ControlTotal" table="CTL_TOT">
        <composite-id name="id" class="ControlTotalID">
            <key-many-to-one name="fileLoad" class="FileLoad"
                column="FL_LOAD_ID" />
            <key-property name="controlTotalTypeCode" type="integer"
                column="CTL_TOT_TYP_CD" />
        </composite-id>
        <property name="controlTotal" type="string" column="CTL_TOT" />
        <property name="createdByID" type="string" column="CRE_BY_ID" />
        <property name="createdDatetime" type="timestamp" column="CRE_DTTM" />
        <property name="modifiedByID" type="string" column="MOD_BY_ID" />
        <property name="modifiedDatetime" type="timestamp" column="MOD_DTTM" />
    </class>
    <!-- Added for loading wfg transaction ids from BE017 and BNKACH feeds to the EDB table  EDBREF.WFG_ACH_PAYMENTS-->
    <class name="WfgTransactionIdBean" table="EDBREF.WFG_ACH_PAYMENTS">
        <id name="traceNumber" type="string" column="TRACE_NUMBER"></id>
        <property name="sourceSystem" type="string" column="SOURCE_SYSTEM" />
        <property name="transactionDate" type="timestamp" column="TRANSACTION_DATE" />
        <property name="processed" type="string" column="PROCESSED" />
    </class>
</hibernate-mapping>

Instead of PROC_RUN_ID_SEQ I am getting-> DEBUG main SQL logStatement:92 - select hibernate_sequence.nextval from dual

What is unclear with this error message?

Jens
– Jens

2022-07-29 09:25:14 +00:00
Commented Jul 29, 2022 at 9:25 — Jens
– Jens, Commented Jul 29, 2022 at 9:25

Joe Taras · Accepted Answer · 2022-07-29 13:57:01Z

1

The causes can be different:

The name of the sequence you have mapped in your pojo is different
You have no permission to use sequence
You have no defined a sequence on your database

Solutions

Fix the sequence name on your pojo
Grant the sequence for your database user
Add the sequence on your database

EDIT AFTER UPDATED QUESTION

Try as follow:

<id name="propName" type="long" unsaved-value="null">
        <column name="columnName" not-null="true" unique="true"
            index="pkName" />
        <generator
            class="org.hibernate.id.enhanced.SequenceStyleGenerator">
            <param name="optimizer">none</param>
            <param name="increment_size">1</param>
            <param name="sequence_name">PROC_RUN_ID_SEQ</param>
        </generator>
</id>

edited Jul 29, 2022 at 13:57

answered Jul 29, 2022 at 9:26

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

StormBreaker Over a year ago

I have updated the question can u plz have a look

Joe Taras Over a year ago

@StormBreaker check my updated answer

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related