0

I have a database column named Record ID . This is a primary key and its value is generated using an oracle sequence which has the below properties Sequence Details Min Value : 1 max Value : 9999999999999999999999999999 increment by : 10

In my Java application , I insert records into the table after applying some business logic . The value for the field Record ID , is got from a sequence generator

Before inserting a record , in Java code I first set its ID in a Java Long Variable with below code ( got from a sequence generator )

public static final String recordSequenceSQL= "SELECT RECORD_ID_SEQ.NEXTVAL FROM DUAL"

Long recordID = jdbcTemplate.queryForObject(SELECT_EVENT_LOG_SEQUENCE_SQL, Long.class);

recordDao.save(recordID , record_name ,age);

The recordID value shows as -8742538778 ( after I receive this value from sequence generator) , which has a minus sign in front of it .

I need to make sure that there is no negative values received . I'm not sure of the root cause and also need a solution to the same

Shatz
  • 31
  • 5
  • If it increments by 10, how come that the last sequence digit is 8? Anyway: if sequence number *is* correct, apply the ABS function to the value (or multiply it by -1) and you'll get a positive number. That's pretty much all I can say about it, regarding I don't know Java. – Littlefoot Sep 24 '20 at 10:18
  • If this sequence is supposed to provide the PK for the table, then don't have your app try to capture and use it. Just create a trigger on the table to pull the sequence and provide it. Or if your db is version is 12c or greater, define the PK as an identity column and let Uncle Oracle take care of it for you. – EdStevens Sep 24 '20 at 12:20

2 Answers2

2

To trouble shoot try the following steps

Check the Sequence

Connect with your application user and run the following query:

select SEQUENCE_OWNER, SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER
from all_sequences 
where SEQUENCE_NAME = 'RECORD_ID_SEQ';

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE                   INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------------------------- ------------ ---------- -----------
<owner>                        RECORD_ID_SEQ                           1 9999999999999999999999999999           10         20     1202020

The important value is in the column LAST_NUMBER which is approximately the value you get by calling the NEXTVAL.

You will not see a negative number (if so contanct Oracle support immediately).

What you should see is a modest number as in may example.

One potential problem is if you see a number that is higher than 9223372036854775807 which is the max value for Long, but is valid number below the sequence MAX_VALUE.

But even in this case you will not get a negative number from your JdbcTemplate query - instead you will get an Numeric Overflow exception

Caught: org.springframework.jdbc.UncategorizedSQLException: 
StatementCallback; uncategorized SQLException for SQL [SELECT RECORD_ID_SEQ.NEXTVAL FROM DUAL]; 
SQLstate [99999];
error code [17026]; 
Numeric Overflow; 
nested exception is java.sql.SQLException: Numeric Overflow

Summary

The problem with the negative value is most probably in the SELECT_EVENT_LOG_SEQUENCE_SQLquery (which you did not show).

Further Suggestions

While using the Long type for the ID column adjust the MAX_VALUE of the sequence to the max value of LONG see here for further discussion

If you expect heavy used sequence, do not use INCREMENT_BY 10, but stay on 1

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • 1
    To the suggestion you made "The problem with the negative value is most probably in the SELECT_EVENT_LOG_SEQUENCE_SQLquery" . This helped . Just before the update query the SQL types defined was TYPES.INTEGER , where it should have been TYPES.BIGINT – Shatz Sep 25 '20 at 09:48
0

After Marmite Bomber pointed out that issue might be in the SQL. Made the below corrections Before
public void saveRecordData(Long RecordId, RecordLog RecordLog) { Object[] parameters = {recordID, RecordLog.getRouteId(), RecordLog.getRecordType().getRecordTypeId(), RecordLog.getOrderId(), RecordLog.getIncomingRecordTimestamp(), RecordLog.getOutgoingRecordTimestamp(), RecordLog.getStatus()}; int[] types = {Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR}; int rowsAffected = jdbcTemplate.update(INSERT_Record_LOG_SQL, parameters, types); }

After ( changed the SQL Type from TYPES.INTEGER to TYPES.BIGINT ) public void saveRecordData(Long RecordId, RecordLog RecordLog) { Object[] parameters = {recordID, RecordLog.getRouteId(), RecordLog.getRecordType().getRecordTypeId(), RecordLog.getOrderId(), RecordLog.getIncomingRecordTimestamp(), RecordLog.getOutgoingRecordTimestamp(), RecordLog.getStatus()}; int[] types = {Types.BIGINT, Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR}; int rowsAffected = jdbcTemplate.update(INSERT_Record_LOG_SQL, parameters, types); }

Shatz
  • 31
  • 5