Oracle Posting Delay

I have a work order counter stored in an oracle database. When someone adds a new work order, this number is retrieved from the database and incremented by one and stored back. There is a one or two second delay before anyone else on the network sees the change. If someone else adds another workorder during this time, they get the same number. They get duplicate work order numbers. How can these be avoided?

BTW, I am using DBISaveChanges to flush any cache.
LVL 2
CalvinDayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
hi calvin,

why do you not use a sequence for this job?

to create the sequence use a statement like this

CREATE SEQUENCE SequenceName
 INCREMENT BY 1
 START WITH 1
 MINVALUE 1
 MAXVALUE 999999999999999999999999999
 NOCYCLE
 NOORDER
 NOCACHE

to get a value from the sequence yóu can use a function like this


Function TForm1.NextVal(const pSequenceName : String) : Integer;
begin
  QrySEQ.Close;
  QrySEQ.SQL.Clear;
  QrySEQ.SQL.Add('SELECT ' + pSequenceName + '.nextval as NXT from dual');
  QrySEQ.Execute;
  Result := QrySEQ.Field('NXT');
end;

the value is then incremeted automatically by the oracleserver and is available in ~0.05 sec

meikl
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CalvinDayAuthor Commented:
How can you reset the sequence number if desired?
0
kretzschmarCommented:
hi calvin,

drop and recreate :-)
thanks for accepting
good luck again

meikl ;-)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

CalvinDayAuthor Commented:
>drop and recreate :-)

I thought no, there must be an easier way.

But straight from the Oracle help:

Altering Sequences
You can change any of the parameters that define how corresponding sequence numbers are generated; however, you cannot alter a sequence to change the starting number of a sequence. To do this, the sequence must be dropped and re-created.

Don't doubt the expert.
0
kretzschmarCommented:
hi calvin,

its not wrong to doubt the expert, because nobody is a really expert,
which knows all, and had you found
another easier way, then had i learned
something from you.

meikl ;-)
0
CalvinDayAuthor Commented:
I have implemented this in our program. It works great. Thanks, again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.