| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Need help with sequence numbers.
A copy of this was sent to Sam <sam_at_hasc.com> (if that email address didn't require changing) On Sat, 21 Mar 1998 16:24:52 -0500, you wrote:
>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.
>
if you want to use a stored procedure to do this, it might look like:
create function new_order( p_ordername in varchar2 ) return number as
l_orderid number;
begin
select order_seq.nextval into l_orderid from dual;
insert into table1 ( ordername, orderid ) values ( p_ordername, l_orderid);
return l_orderid;
end;
Else, you can just just code in your application:
insert into table1 (ordername,orderid) values('abc', order_seq.nextval);
and whenever you want to get that order id just:
select order_seq.currval INTO :somevariable from dual;
>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
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Mar 22 1998 - 00:00:00 CST
 |
 |