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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kretzschmarConnect With a Mentor Commented:
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
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.