sequence.nextval and sequence.currval [message #351921] |
Fri, 03 October 2008 13:35  |
ziggy25
Messages: 206 Registered: July 2005
|
Senior Member |
|
|
Hi All,
I am trying to write some data into two tables(Shown below).
The DCN_SEQ column on the DECLARATION table is populated from a sequence.
The value of this sequence is then used in the second table. i.e. dcn_itinerary.dcn_seq.
The code below is just a snippet of the actual code. The value of dcn_seq.currval is used on about 7 other tables before a commit takes place. I am saving this value in a variable called lv_dcn_seq_currval right after the first insert and using the local variable instead.
My question is that the use of the sequence number (currval) is concerning me a bit. The COMMIT will happen at the end of the code. I am wondering if this is a safe way to do this. Are there any situations where this might be a problem?
I did think of committing after the first Insert but then that means i cant rollback.
If this is not safe, is there a better/safer way of achieving the same thing?
Insert into declaration (DCN_SEQ,
DCN_VERSION_NUM,
DCN_TYPE,
DCN_PARTICIPANT_ID,
FLT_SEQ,
LRN,
NATIONALITY_CROSSING_BORDER,
TRANSPORT_MODE_AT_BORDER,
CONVEYANCE_REF_NUM,
OFFICE_OF_1ST_ENTRY,
EXPECTED_ARR_DTM)
Values (dcn_seq.nextval,1,'I',participant_id_in,flt_seq_in,lv_lrn,
lv_flight_country_code,4,lv_carrier_code||lv_flight_number,
lv_aoe_customs_off,lv_aoe_arr_dtm);
select dcn_seq.currval into lv_dcn_seq_currval from dual;
-- Insert Itinerary record
For flight_itinerary_rec In flight_itinerary_cur(flt_seq_in)
Loop
Insert into dcn_itinerary(dcn_seq,
dit_seq,
loc_code)
Values(lv_dcn_seq_currval,dit_seq.nextval,
flight_itinerary_rec.orig_loc_id);
If (flight_itinerary_rec.apt_of_entry_ind='Y') then
Insert into dcn_itinerary(dcn_seq,
dit_seq,
loc_code)
Values(lv_dcn_seq_currval,dit_seq.nextval,
flight_itinerary_rec.dest_loc_id);
End If;
End Loop;
[Updated on: Fri, 03 October 2008 13:39] by Moderator Report message to a moderator
|
|
|
|
Re: sequence.nextval and sequence.currval [message #351953 is a reply to message #351927] |
Fri, 03 October 2008 16:38   |
ziggy25
Messages: 206 Registered: July 2005
|
Senior Member |
|
|
Hi,
Thanks for clarifying that.
What exactly will happen if another transaction makes use of dcn_seq.nextval and does a commit before my transaction does a commit?
Will it get the nextval of the value i used even though i have not commited yet? If it does what happens if i rollback? Will the value never be used?
Thanks
|
|
|
Re: sequence.nextval and sequence.currval [message #351956 is a reply to message #351953] |
Fri, 03 October 2008 17:27   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Sequences are not involved in COMMITs. Once you allocate a number from a sequence with NEXTVAL, it is allocated for good. If you ROLLBACK your transaction, you will never re-use that number.
Ross Leishman
|
|
|
|