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

"vijay" <vksinghbhu_at_gmail.com> schreef in bericht news:a14c82fc-a502-4baf-99cb-fddefbe1e812_at_d21g2000prf.googlegroups.com...
> 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
>
>

So if I understand this code, you try to generate a number from a sequence, and if anything fails you create the sequence because the only exception could be a non existing sequence? Are you sure 'abi_'||strkeyname is always a valid sequence name?

One of the errors I see is that when the sequence is created, no value is returned.
Why don't you just use one sequence for all? Or is this another way of trying to get non-gapped sequence numbers? Or do you think you'll run out of numbers?

Shakespeare Received on Fri Mar 07 2008 - 05:06:37 CST