How to Change Sequence current value to the previous value?

Question

In my project I have one transaction function. If that transaction failed then all the data is reverted to the previous stage.

Consider that, in that transaction, I am using sequence's next value to insert value to the table. If the transaction failed, then I want to reset or change the sequence to the previous value.

If it's possible, give me some example.

I tested it, but I got the error:

java.sql.SQLException: ORA-08002: sequence TEST.CURRVAL is not yet defined in this session

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)

Here is my code:

        String sql1 = "select test.currval from dual";
        pst = conn.prepareStatement(sql1);
        rs = pst.executeQuery();
        int currentValue = 0;
        if(rs.next()){
            currentValue = rs.getInt(1);
        }
        currentValue -= 1;
        String sql2 = "alter sequence Test start with '"+currentValue+"'";
        pst = conn.prepareStatement(sql2);
        pst.execute();
        pst = conn.prepareStatement(sql1);
        rs = pst.executeQuery();
        if(rs.next()){
            JOptionPane.showMessageDialog(null, rs.getInt(1),"Current Sequence Number",JOptionPane.INFORMATION_MESSAGE); ;
        }

score 3 · Accepted Answer · edited May 23 '17 at 11:51

I will try to tell not how to do it but why you shouldnt.

What you trying to achieve defeats the purpose of sequence. Sequence is there to give you a unique identifier, doesnt matter if you skip some values. One day or another, trying to rollback to an old value especially where you have concurrent transactions ( you may not have today, but you probably will make several calls sometime either on purpose or by accident) will lead you serious problems.

So i would advise dont do it.

Please note, changing sequence number to solve a problem manually and doing it as a part of flow is different, what i am trying to say that dont do it as a part of your code flow.

Still, a search lead me this answer. Recommend to read it. https://stackoverflow.com/a/93633/2482288

Thank you for your suggestion, it's really helpful for me. – Thirunavukkarasu Jun 26 '15 at 07:12 — Thirunavukkarasu, Jun 26 '15 at 07:12

score 1 · Answer 2 · answered Jun 26 '15 at 06:46

The ORA-08002 means that you have tried to get the sequence current value before invoking the sequence next value. It's illegal in Oracle.

SELECT test.CURRVAL
FROM dual; -->illegal

SELECT test.NEXTVAL
FROM dual;
SELECT test.CURRVAL
FROM dual; -->legal

Please read this post to get a better understanding.

How to Change Sequence current value to the previous value?

2 Answers2