Oracle sequence does not increment from Java/Hibernate

Question

I have the following function which calls nextval on an Oracle sequence, but i noticed a strange behavior. The function is :

public class DAOImplementation{
    private static SessionFactory factory;
    private Session session;

    public BigDecimal getSequenceNextVal(){
        BigDecimal result = null;
        factory = HibernateUtil.getSessionFactory();
        session = factory.openSession();
        String sql = "select sequence_test.nextval from dual";
        Query query = session.createSQLQuery(sql);
        result = (BigDecimal) query.uniqueResult();
        session.close();
        return result;        
    }
}

When I print in console the value returned by the above function, I can see the correct next value. But, when I run on DB "select sequence_test.currval from dual" the value is not corresponding to the one in the console. If I reconnect to the Oracle DB from PL/SQL Developer and run the "select sequence_test.nextval from dual", the result is incremented by 2(i guess once by the java function and once more by the query executed manually).

Can you please help me to understand this situation?

StuPointerException · Accepted Answer · 2017-02-23 14:14:38Z

2

sequence.currval is linked to your database session and is not actually the current value of the sequence in the sense that you're referring. Essentially currval will return the last value generated by nextval for a given session.

This is because the sequence is likely being changed by multiple sessions so the idea of a session independent current value rarely makes sense.

The name of this property is certainly confusing though...

answered Feb 23, 2017 at 14:14

StuPointerException

7,2775 gold badges31 silver badges56 bronze badges

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

2 Comments

Mark J. Bobak Over a year ago

Yep,what he said....I was just beginning to write virtually the same answer when I saw this got posted....

andcsie Over a year ago

Thank you for your answer. I made a similar function for getting current value from java source and the results match.

Collectives™ on Stack Overflow

Oracle sequence does not increment from Java/Hibernate

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