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

"Shakespeare" <whatsin_at_xs4all.nl> schreef in bericht news:47d121c7$0$14345$e4fe514c_at_news.xs4all.nl...
>
> "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
>

On second thought (I just realized I saw 'multithreaded'): what if two threads try to select at the same time and the sequence does not exist? Both raise an exception; The first one will create a sequence, and the second one will generate an (unhandled)exception ORA-00955.

So what value do you assign then, must be somewhere in the rest of your code (if keyval is null then....) or something like that, or you just leave them as null. In both cases, you have a duplicate key

If you have a nullable unique key, you get two null values in this case.

Solution (for this code, I'm not too happy with it): get the value in the exception as well, and handle the ora-955 exception.

Better: use the same sequence for all keys.

Shakespeare Received on Fri Mar 07 2008 - 05:31:32 CST