Re: Oracle sequence returning duplicate key for a given key name
Date: Sun, 9 Mar 2008 14:35:28 +0100
Message-ID: <47d3e7ac$0$14358$e4fe514c@news.xs4all.nl>
"vijay" <vksinghbhu_at_gmail.com> schreef in bericht
news:173b1b34-e424-4033-ac6c-f0f4112c1f60_at_i7g2000prf.googlegroups.com...
On Mar 7, 5:39 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Hi Steve,
Let change stored procedure like
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;
end;
end;
Stored Procedure suppose sequence is already exists for a given key name.
If program run by 24X7 first after 8 or 10 hours stored procedure return duplicate value for a given key name. This behavior is random.
I can say first x generated key is unique then next new key is duplicate then again next y generated key is unique and then next new key is duplicate and so on.
where x and y is any positive number i.e. 100,101,102,103 etc.
Thanks,
Vijay
The only reason I can come up with is that your treads have created the sequence in different schemas, and you have now two sequences with the same name. Otherwise it is impossible for a sequence to return the same sequence number twice.
With a 'select from all_sequences' you could check this.
Shakespeare Received on Sun Mar 09 2008 - 08:35:28 CDT