Jak sekwencja.nextval może być zerowa w Oracle?

Mam zdefiniowaną sekwencję Oracle:

CREATE SEQUENCE  "DALLAS"."X_SEQ"  
    MINVALUE 0 
    MAXVALUE 999999999999999999999999999 
    INCREMENT BY 1 START WITH 0 NOCACHE  NOORDER  NOCYCLE ;

Jest używany w procedurze przechowywanej do wstawienia rekordu:

PROCEDURE Insert_Record
                (p_name    IN  VARCHAR2,                
                 p_userid  IN  INTEGER,
                 cur_out   OUT TYPES_PKG.RefCursor)
    IS
        v_id NUMBER := 0;
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO v_id
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO X
            (the_id,            
             name,                        
             update_userid)
          VALUES
            (v_id,
             p_name,                        
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT v_id the_id
              FROM dual;
    END;

Czasami ta procedura zwraca błąd po uruchomieniu z kodu aplikacji.

ORA-01400: cannot insert NULL into ("DALLAS"."X"."THE_ID") 
ORA-06512: at "DALLAS.X_PKG", line 40 
ORA-06512: at line 1

Szczegóły, które mogą, ale nie muszą być istotne:

• Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produkcja 64-bitowa
• Procedura jest wykonywana za pośrednictwem Microsoft.Practices.EnterpriseLibrary - Data.Oracle.OracleDatabase.ExecuteReader (polecenie DbCommand)
• Aplikacja nie zawija połączenia w jawną transakcję.
• Wkładka nie działa sporadycznie - mniej niż 1%

W jakich okolicznościach może x_seq.nextvalbyć zerowy?

Jestem pewien, że skończy się to artefaktem twojego kodu lub sterownika .net, którego używasz. Przygotowałem dla ciebie szybkie demo, używając czystego SQL - PL / SQL i nigdy nie otrzymałem utraconej wartości sekwencji. Nawiasem mówiąc, kursor ref, którego używasz, jest prawdopodobnie niepotrzebny i prawdopodobnie wpływa na wydajność i czytelność kodu - moje demo zawiera procedurę insert_record2, która konsekwentnie wykonuje ponad 10% szybciej - około 26 sekund na moim laptopie w porównaniu z 36 w przypadku wersji kursora ref. Przynajmniej uważam, że łatwiej to zrozumieć. Możesz oczywiście uruchomić zmodyfikowaną wersję dla testowej bazy danych wraz z wyzwalaczem kontroli.

/* 
demo for dbse 
assumes a user with create table, create sequence, create procedure pivs and quota. 

*/

drop table dbse13142 purge;

create table dbse13142(
    the_id number not null
,   name   varchar2(20)
,   userid number)
;

drop sequence x_seq;
CREATE SEQUENCE  X_SEQ NOCACHE  NOORDER  NOCYCLE ;

create or replace PROCEDURE Insert_Record
                (p_name    IN  VARCHAR2,                
                 p_userid  IN  INTEGER,
                 cur_out   OUT sys_refcursor)
    IS
        v_id NUMBER := 0;
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO v_id
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,            
             name,                        
             userid)
          VALUES
            (v_id,
             p_name,                        
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT v_id the_id
              FROM dual;
    END;
/


create or replace PROCEDURE Insert_Record2
                (p_name    IN  VARCHAR2,                
                 p_userid  IN  INTEGER,
                 p_theid   OUT dbse13142.the_id%type)
    IS
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO p_theid
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,            
             name,                        
             userid)
          VALUES
            (p_theid,
             p_name,                        
             p_userid);
    END;
/

set timing on

declare
   c sys_refcursor;
begin   
for i in 1..100000 loop
   insert_record('User '||i,i,c);
   close c;
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;

declare
  x number;
begin   
for i in 1..100000 loop
   insert_record2('User '||i,i,x);
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;

Spróbuj zrobić przypadek testowy. Zrób tabelę zastępczą i wstaw 100 000 rekordów, używając sekwencji z bazy danych. Założę się, że nie będziesz mieć problemów. Następnie spróbuj wstawić to samo z aplikacji.

Czy może to być spowodowane innymi problemami, takimi jak niedopasowanie klienta Oracle?

Innym rozwiązaniem, które rozwiązałoby problem, ale nie stanowi problemu, jest dodanie wyzwalacza na stole.
Przed wstawieniem do tabeli w Dallas.X JEŻELI: identyfikator_i jest zerowy NASTĘPNIE WYBIERZ x_seq.nextval INTO: identyfikator_ID z dual; KONIEC JEŻELI;

Nie mam jeszcze uprawnień do komentowania, więc pisząc to jako odpowiedź: Ponieważ używasz wersji Oracle> = 11.1, która zezwala na sekwencje w wyrażeniach PL / SQL zamiast w SQL, spróbuj tego:

   v_id := x_seq.nextval;

Zamiast tego:

 -- Get id value from sequence
    SELECT x_seq.nextval
      INTO v_id
      FROM dual;

Lub chociaż słyszałem wątpliwości / pułapki podczas używania „.currval”, może pomijam oddzielne przypisanie v_id i używam tylko tego kodu ?:

 -- Line below is X_PKG line 40
        INSERT INTO X
            (the_id,            
             name,                        
             update_userid)
          VALUES
            (x_seq.nextval,
             p_name,                        
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT x_seq.currval the_id
              FROM dual;

Przepraszam, nie mam teraz przydatnej instancji 11g, aby to wypróbować.