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

On Mar 7, 10:57 am, vijay <vksingh...@xxxxxxxxx> wrote:
On Mar 7, 5:39 pm, Steve Howard <stevedhow...@xxxxxxxxx> wrote:





On Mar 7, 1:13 am, vijay <vksingh...@xxxxxxxxx> 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
.

Relevant Pages