How Can I get the last inserted sequence value for respective to a web session in JSP and Oracle? -
first of beg request you, please not treat duplicate. have seen threads issue none of type.
developing online registration system using jboss 6 , oracle 11g. want give every registrant unique form number sequentially.
this, think oracle's sequence_name.nextval primary key field best inserting unique yet sequential number , retrieving same use sequence_name.currval. till hope, it's ok.
ensure parity if 2 or more concurrent users submits web form simultaneously? (i mean there overlap of interchange of value among concurrent users?) more precisely, session dependent?
let me give 2 hypothetical situations matter becomes clearer.
say there 2 users, user1 , user2 trying register @ same time sitting @ newyork , paris respectively. max(form_no) 100 before click submit button. now, in code have written
insert member(....) values(seq_form_no.nextval,....).
now since 2 users invoke same query sitting @ 2 different terminals own sequential id or user1 user2's or vice-versa? hope made issue clear. see, sequence unique, know, want associate ids inserted respectively. in advance.
i'm not sure understand. said, senquence
ensure uniqueness of generated number among concurrent transactions/connections. unless if sequence created cycle
option, within transaction, can rely on strictly monotonically increasing (resp. decreasing) numbering. not absence of gap (probably expecting when talking "sequential numbers").
worth mentioning sequence numbers never go backward. when acquires value, "consumed" sequence , never inside (beside cycle
) -- if rollback current transaction.
from the doc (emphasis mine):
when sequence number generated, sequence incremented, independent of transaction committing or rolling back. if 2 users concurrently increment same sequence, sequence numbers each user acquires may have gaps, because sequence numbers being generated other user. one user can never acquire sequence number generated user. after sequence value generated 1 user, user can continue access value regardless of whether sequence incremented user.
my jsp little bit ... "rusty", like work expected:
<sql:update datasource="${ds}" var="result"> insert member(....) values(seq_form_no.nextval,....); </sql:update> <sql:query datasource="${ds}" var="last_inserted_member_id"> select seq_form_no.currval dual; </sql:query>
Comments
Post a Comment