0

I need an advice about the counter of incoming requests for Java ejb web service. Application server where a web service is deployed has 4 nodes, so the incoming request can be processed on any of 4, and I decided to store counter value in Oracle database. I set the current number of request by calling select SEQPACKSCYCLETOT.NEXTVAL from dual and it works fine.

But I also need to show current counter value, and here is a problem. When I call select SEQPACKSCYCLETOT.CURRVAL from dual sometimes I get an exception sequence SEQPACKSCYCLETOT.CURRVAL is not yet defined in this session.

What session is it about and how to work with it? Here is the code snippet:

@Singleton
public class DbWorkerImpl implements DbWorker {
@Override
public Long getPacksTotSeqValue() throws SQLException {
    String query = "select SEQPACKSCYCLETOT.CURRVAL from dual";
    Long packetsCount = null;
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        conn = ds.getConnection(Username, Password);
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query);
        if (rs.next())
            packetsCount = rs.getLong(1);
    } 
    catch (SQLException e) {
        logS.debug(this.servername + e.getMessage());
    }
    finally{
        if (rs!=null) rs.close();
        if (stmt!=null) stmt.close();
        if (conn!=null) conn.close();
    }
    return packetsCount;
}
Buch
  • 97
  • 1
  • 10
  • Please show relevant code. and complete error stack trace. – OldProgrammer Jun 12 '17 at 12:36
  • How many Oracle instances do you have running there? – JimmyB Jun 12 '17 at 12:42
  • @JimmyB What do you mean Oracle instances? Database is only 1. EJB class is like this: `@Singleton public class DbWorkerImpl implements DbWorker` – Buch Jun 12 '17 at 13:10
  • Please edit your question, and post code in the question body, not as a comment. Nobody can read that. Thanks, – OldProgrammer Jun 12 '17 at 13:12
  • Possible duplicate of [Oracle: sequence MySequence.currval is not yet defined in this session](https://stackoverflow.com/questions/809247/oracle-sequence-mysequence-currval-is-not-yet-defined-in-this-session) – JimmyB Jun 12 '17 at 13:12
  • In short, you need to call `NEXTVAL` at least once in each session before you can read `CURRVAL`. – JimmyB Jun 12 '17 at 13:13
  • @JimmyB I tested it on a server with only 1 node, not a cluster, but got the same issue. What session does it mean? – Buch Jun 12 '17 at 13:17
  • 1
    Database "session", i.e. connection. In an app server with connection pooling it's hard to tell when a new connection to the RDBMS is made, so don't rely on it. Note that `CURRVAL` is supposed to let you retrieve the last value *you* got from `NEXTVAL` again. – JimmyB Jun 12 '17 at 13:20
  • @JimmyB It is what I need - the last value set by NEXTVAL. But I call nextval when request comes, instead of that I call CURRVAL every n seconds to show the counter to the user. Looks like its just a bad idea to use sequence for a counter :( – Buch Jun 12 '17 at 13:28

0 Answers0