Oracle-L: RE: nextval increments by 2 in pl/sql procedure

From: Naveen Nahata <naveen_nahata_at_mindtree.com>
Date: Wed, 14 Aug 2002 04:53:42 -0800
Message-ID: <F001.004B42AE.20020814045342@fatcity.com>

There is nothing wrong in what you are doing. It might be that there are lots of requests for story_transaction_seq.NEXTVAL and by the time you reach the next line it gets incremented by 1 again(unlikely, but possible).

I suggest you test it when you are sure that there will be no request for the value from the sequence by any other session.

Naveen

-----Original Message-----
Sent: Wednesday, August 14, 2002 4:04 AM To: Multiple recipients of list ORACLE-L

hello all,

I have a pl/sql procedure that selects the nextval from a sequence. The problem is that it incrrements the sequence by 2 in a pl/sql procedure. If I select the nextval from a sqlplus session, it works fine.

here is partial code that I am using.

PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type,

                 		            v_transid OUT

story_transaction.trans_id%type ) IS

BEGIN
select story_transaction_seq.nextval into v_transid from dual; .....
END for debugging purposes, I made that code look like...

PROCEDURE NEWTRANSACTION (v_storyid IN story.story_id%type,

                 		            v_transid OUT

story_transaction.trans_id%type ) IS

v_value1 NUMBER; --debug code
v_value2 NUMBER; --debug code

BEGIN

  select story_transaction_seq.currval into v_value1 from dual; --debug code
  select story_transaction_seq.nextval into v_transid from dual;
  select story_transaction_seq.currval into v_value2 from dual; --debug code

.....
END when I ran this, v_value1 had a 200, v_transid had a 201, and v_value2 had a 202

taking the debug code out and hitting that procedure with a web page will increment the v_transid by 2 each refresh of the page.

am i doing something wrong? I can't find any information on the web related to this issue.

TIA Danny Hughes
DBA
Knobias.com
601-978-3399 x103
dhughes_at_knobias.com
www.knobias.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Danny Hughes
INET: DHUGHES_at_knobias.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Naveen Nahata
INET: naveen_nahata_at_mindtree.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------