Home » SQL & PL/SQL » SQL & PL/SQL » sequence.nextval and sequence.currval (Oracle 10g)
sequence.nextval and sequence.currval [message #351921] Fri, 03 October 2008 13:35 Go to next message
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 #351927 is a reply to message #351921] Fri, 03 October 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am wondering if this is a safe way to do this. Are there any situations where this might be a problem?

Yes, it is safe. No, there will be no problem.

Keep your code lines in 80 characters.

Regards
Michel
Re: sequence.nextval and sequence.currval [message #351953 is a reply to message #351927] Fri, 03 October 2008 16:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: sequence.nextval and sequence.currval [message #351957 is a reply to message #351921] Fri, 03 October 2008 17:35 Go to previous message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Ok its clear now.
Thank you all for your help.
Previous Topic: While printing refcursor " ORA-01858 " is coming.
Next Topic: How to save output to a table
Goto Forum:
  


Current Time: Fri Dec 02 14:06:07 CST 2016

Total time taken to generate the page: 0.18147 seconds