assigning a gauranteed incrementing number with no misses to a column

[Jeevan Sunkersett]

Hi,

I am using Hibernate 3.0 with Oracle as my database and have a hard requirement like

I need to populate a table say XYZ, which has a column receiptNumber.
The hard requirement is that for each row, in the table the receiptNumber steadily incrementing BUT WITH NO MISSES allowed

In other words suppose the table has records, with receiptNumber say,
10001,
10002,
10004 (note 10003 has been missed ... a not acceptable situation)

For this initially I had thought of using a a sequence to generate the receiptNumber but in case of exceptions it is possible the sequence increments but no data gets inserted (leading to the missing number situation)

So my DBA suggested, a PL/SQL procedure, in which a number will selected from a table via

select rNumber from rcptNumber_TAB for update
--followed by
insert into table XYZ
insert rNumber + 1 into rcptNumber_TAB
commit

This way their will be NO misses in the incrementing rcpt Number inserted.

Could the above be achieved exclusively using java + hibernate and no PL/SQL?

Any ideas will be appreciated.

thank you.
Jeevan

[Rajan Chinna]

When you construct your object, you can hit the DB and get next primary/sequence key from rcptNumber_TAB entity.
Ex:
XYZ p = new XYZ(someservice.getNextPrimaryKey());
p.setFirstName(first);
p.setLastName(last);
somedao.save(p);

Also you could use trigger to fetch next key.

[Jeevan Sunkersett]

Thank you Rajan,

In my case the reciptNumber is not a primary key; but yes what you mention seems pausible.

I understand someservice.getxxxx() .... is DAO or similar

which will do a select rNumber from rcptNumber_TAB for update ................. trip 1
next save to the main table XYZ .....................................................................trip 2
followed by updating rcptNumber_TAB with incremented rcptNumber .............trip 3

I guess I should encapsulate everything into a single transaction.

But what I see here are multiple trips (as mentioned) from java-application-server to oracle-server.

will that not degrade performance?

PS: My DBA says use of triggers is a bad practice.

~g1

[Arun Kumarr]

What's stopping you from making the column a primary key?
Won't it solve the issue.