Hi Gang,

One of my module has oracle log file of all transactions in a particular accounting screen. This file will be added with transaction type as 'A' in case of addition and 'C' in case of change mode etc, when user confirm the screen.

This file has only one key field ie sequence number.

The logic behind addition is - >

LS11 - Add mode of file - IACTEMP1
Before read logic -
LSCALL(12)
IAC-SEQ = CONV(##LAST-SEQ + 1)

call to LS12, to get last sequence number in the table and increment it and assign to key field.


LS12 - Def logic -

SQL
SELECT FROM IACTEMP1
WHERE IAC_SEQ =
(SELECT MAX(IAC_SEQ) FROM IACTEMP1)
ENDSQL

The above is working fine. But it is failing when both users on the same screen and do confirm of the operation on same time.
One user is getting integrity constaint sql problem.
I suspect the problem - As both users are getting the same last sequence number and trying to add with same next number, this problem arised.

How to avoid this simultaneous users problem. Let me know the best action to be taken for this problem.

I tried to add RETRY_READ(), but not working.

How to use oracle sequence in proiv?

Thanks in Advance .....

Sudhakar Chunduri.

I'm not an expert, but I would use an oracle seq rather than your own file, in pro-iv all you need is some inline SQL:

SQL
select seq_name.nextval
from dual
ENDSQL

where seq_name is the name of the oracle sequence you have set up.

this will stop the problem of 2 people getting the same sequence number.

sorry, I should say

SQL
select seq_name.nextval
into :#variable
from dual
ENDSQL

Just beware when it comes to Oracle Sequences. If you cache your sequence numbers to x number of sequences, if the database closes abnormally you lose those sequence numbers.

Have you tried assigning sequence numbers and committing that change immediately ?

Or make the sequence number your terminal no and you all your processing first. Once you finished processing, update your table with a sequence no.