Oracle sequence returning duplicate key for a given key name

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 Received on Fri Mar 07 2008 - 00:13:32 CST