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

On Mar 7, 1:13 am, 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
Hi Vijay,

What does...

select created from dba_objects were object_name like 'ABI%' and object_type = 'SEQUENCE' ;

...return?

Is someone dropping the sequence on a regular basis that you feel the need to do DDL in an exception handler?? Something doesn't jibe there...

As Shakespeare noted, the only way a duplicate *value* could be generated (in *your* code) is if the sequence doesn't exist, and a null is returned (because you don't set nkeyvalue in your exception handler). The null would be the duplicate value.

Regards,

Steve Received on Fri Mar 07 2008 - 06:39:56 CST