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

From: <fitzjarrell_at_cox.net>
Date: Fri, 7 Mar 2008 12:44:09 -0800 (PST)
Message-ID: <5c9999f3-b386-4334-8129-0e1074a1229d@b1g2000hsg.googlegroups.com>

On Mar 7, 10:57 am, vijay <vksingh..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

'Fat fingering' the strkeyname passed to this glorious stored procedure could cause any number of problems, including your apparent 'duplicate key' issue. If you're truly using the same sequence every time, for the same table, you can't get duplicate keys becuse the sequence is guaranteed to generate unique values. Also, your current stored procedure doesn't return the next value from the newly created sequence, thus making nkeyvalue NULL, as reported by others in this thread. One would think that even with this dismal example the person writing it would have thought enough to actually return the first value from the new sequence. Modifying this abyssmal code:

create or replace PROCEDURE ABI_SP_GETNEWLOGKEY

    (strkeyname IN VARCHAR2, nkeyvalue OUT NUMBER ) is

	no_sequence exception;
	pragma exception_init(no_sequence, -2289);
begin
     EXECUTE IMMEDIATE 'SELECT abi_' ||strkeyname ||'.NEXTVAL FROM
dual'
     INTO nkeyvalue;

EXCEPTION
  WHEN no_sequence THEN

      EXECUTE IMMEDIATE 'create sequence abi_' || strkeyname || ' INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE';       EXECUTE IMMEDIATE 'SELECT abi_' ||strkeyname ||'.NEXTVAL FROM dual'

      INTO nkeyvalue;
  WHEN OTHERS THEN

	dbms_output.put(dbms_utility.format_error_stack);
        dbms_output.put_line(dbms_utility.format_error_backtrace);

end;
/

You would then get a valid sequence number returned, even from an initially missing sequence:

SQL> select object_name
  2 from user_objects
  3 where object_name like 'ABI%'
  4 /

OBJECT_NAME

---

ABI_BLERB
ABI_SP_GETNEWLOGKEY SQL> exec abi_sp_getnewlogkey('cookiemonster', :x)

PL/SQL procedure successfully completed.

SQL> print x

         X

---

         1

SQL> select object_name
  2 from user_Objects
  3 where object_name like 'ABI%'
  4 /

OBJECT_NAME

---

ABI_BLERB
ABI_COOKIEMONSTER
ABI_SP_GETNEWLOGKEY

SQL> I hope you get the idea.

David Fitzjarrell Received on Fri Mar 07 2008 - 14:44:09 CST