Re: Oracle sequence returning duplicate key for a given key name

From: Vince <vinnyop_at_yahoo.com>
Date: Fri, 7 Mar 2008 13:58:00 -0800 (PST)
Message-ID: <9308d80d-cf9e-46d4-8cab-ec97128c927b@e23g2000prf.googlegroups.com>


On Mar 6, 10:13 pm, vijay <vksingh..._at_gmail.com> wrote:
> Tool - VC2005, OleDb
> Database - Oracle 10g
> OS - Window 2003 Server
>
> Hi,
>
> We have developed oracle stored procedure to generate unique key for a
> given key name. Stored procedure uses sequences to return new key.
>
> create or replace PROCEDURE ABI_SP_GETNEWLOGKEY
>     (strkeyname IN VARCHAR2, nkeyvalue OUT  NUMBER )
> is
> begin
>   declare
>   begin
>      EXECUTE IMMEDIATE 'SELECT abi_' ||strkeyname ||'.NEXTVAL FROM
> dual'
>      INTO nkeyvalue;
>   EXCEPTION
>   WHEN OTHERS THEN
>       EXECUTE IMMEDIATE 'create sequence abi_' || strkeyname || '
> INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE';
>   end;
> end;
>
> Above stored procedure is being called from multi threaded vc++
> application. VC++ application uses OleDb to call sp. Some time stored
> procedure return duplicate key for a given key name.
>
> Can anyone suggest me what could be reason.
>
> Thanks in advance.
>
> Vijay Singh
> EDISPHERE
You did not post the original DDL for creating the sequences. Is it possible that the originals are either created with a lower than needed maxvalue and cycled and thus causing the problem? Received on Fri Mar 07 2008 - 15:58:00 CST

Original text of this message