Need help with sequence numbers.

Hi,

I'm creating an order manager for a company that needs a unique id number for each order. The order number will be generated using the sequence.NEXTVAL on Oracle 7.3
The order of commands is as follows:

• client creates new order (ordername is set).
• server adds new record to the table with ordername and generated orderid
• server sends the orderid to the client

My idea of order creation so far is as follows (in pseudo sql, if such thing exists):
command: insert into table1 ordername=abc orderid=Sequence.NEXTVAL command: select orderid from table1 where ordername=abc

My problem is that I don't want to do two transactions to the table every time I create a new order in order to get the orderid. Someone suggested having a stored procedure that returns the orderid when the commands are done.

Can you please tell me if this is the right track to follow, or should I handle the whole thing using a file, or another table?

Thanks in advance.

Sam Habbab
Programmer
sam_at_hasc.com
http://www.hasc.com Received on Sat Mar 21 1998 - 00:00:00 CST