How could sequence.nextval be null in Oracle?

Question

I have an Oracle sequence defined like so:

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

It is used in a stored procedure to insert a record:

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;

Occasionally, this procedure returns an error when executed from application code.

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

Details that may or may not be relevant:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
The procedure is executed via Microsoft.Practices.EnterpriseLibrary - Data.Oracle.OracleDatabase.ExecuteReader(DbCommand command)
The application does not wrap the call in an explicit transaction.
The insert fails intermittently - less than 1%

Under what circumstances could x_seq.nextval be null?

How much code is between the select & insert? Are there any BEGIN..END blocks or any EXCEPTION statements in that code? Is v_id referenced at all in that code? Seems a bit strange. Can you put a "IF v_id IS NULL THEN .... END IF" block directly after the statement and leave some debugging output somewhere if the sequence does in fact assign null to v_id? That or wrap the sequence select in a BEGIN..EXCEPTION block, as there might be something happening that's not been caught. One last thing - is there a trigger on the table you're inserting into that could be causing it? — Philᵀᴹ, Feb 15 '12 at 21:54
@Phil - The select is immediately before the insert. No BEGIN, END, or EXCEPTION other than the proc BEGIN/END. `v_id` is only referenced in the sequence select, the insert, and the final cursor. Our next step was to add the debugging code. We may have to wait for results as it only happens in production and very infrequently. There is a trigger that inserts into an audit table. I've combed through it with no smoking gun. The problem also occasionally occurs in other tables without triggers. Thanks for taking a look. — Corbin March, Feb 15 '12 at 22:14
Only thing I can really think of at the moment is if :new.the_id would somehow become NULL in the trigger that is on table X. — Philᵀᴹ, Feb 15 '12 at 22:22
@Phil: this is most certainly the cause of the problem. You should make it an answer. — René Nyffenegger, Feb 16 '12 at 11:20
@RenéNyffenegger -the problem also occurs in procs that insert into tables without triggers. It appears to be an equal opportunity bug. — Corbin March, Feb 16 '12 at 16:59

score 4 · Accepted Answer · answered Feb 16 '12 at 11:07

I'm pretty certain this will end up being an artifact of your code, or the .net driver you are using. I've knocked up a quick demo for you using pure SQL - PL/SQL and never get a lost sequence value. Incidentally the ref cursor you are using is probably unnecessary and likely impacts performance and readability of the code - my demo includes an insert_record2 procedure that consistently performs over 10% faster -in about 26s on my laptop vs 36 for the ref cursor version. I at least also think is easier to understand. You could obviously run a modified version against your test database complete with audit trigger.

/* 
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;

by the way a version with the traditional approach of using a trigger for the the_id column and the procedure as follows also ran quicker create or replace PROCEDURE Insert_Record3 (p_name IN dbse13142.name%type, p_userid IN dbse13142.userid%type, p_theid OUT dbse13142.the_id%type) IS BEGIN INSERT INTO dbse13142 (name, userid) VALUES (p_name, p_userid) returning the_id into p_theid; END; / — Niall Litchfield, Feb 16 '12 at 13:35
Agreed that it's likely a problem with app code or driver. I'm just curious what could cause a null nextval as a side effect. Puzzling. Thanks for the performance tip. It's good advice that I'll suggest to the team. — Corbin March, Feb 16 '12 at 14:51
Corbin, what I mean (and Kevin) is that something odd is going on between your code and oracle - if you run the test purely in SQL you don't get the effect. But see Phil's comment about the audit trigger (which you could try disabling). — Niall Litchfield, Feb 16 '12 at 16:31
I understand the points made. The problem exists in procs inserting into tables with and without triggers so a trigger isn't required. When a trigger exists, it simply inserts into an audit table. I've confirmed `:new.the_id` is untouched. I understand my question is a long shot. It is resistant to my google-fu and has several people scratching their heads here. I just figured someone might recognize the symptom (and treatment) given enough eyeballs. Thanks for taking a look. — Corbin March, Feb 16 '12 at 16:57

score 2 · Answer 2 · answered Feb 16 '12 at 03:22

2

Try doing a test case. Make a dummy table and insert 100,000 records using your sequence from the database. I'm betting you will have no problems. Next try inserting the same thing from your application.

Could this be caused by other issues such as an Oracle client mismatch?

Another solution that would fix the issue but not problem is to add a trigger on the table.
Before Insert on table on Dallas.X IF :the_id is null THEN SELECT x_seq.nextval INTO :the_id FROM dual; END IF;

answered Feb 16 '12 at 03:22

kevinskio

4,194
1
27
48

I can't recreate the problem locally. It only happens in production and there only infrequently. My hunch is that you're correct about the Oracle client. The problem appeared a couple weeks ago during a release where the client was *not* updated. However, it feels like something isn't getting along between app and db. Interactions from other consumers seem to work fine. The null check isn't a bad idea, but ideally I'd like to get to the root of the problem versus work around it. Who knows though? A work-around is better than broken. – Corbin March Feb 16 '12 at 14:56

score 0 · Answer 3 · answered Feb 17 '12 at 04:05

I don't have priveldges to make comments yet, so writing this as an answer: Since you are using Oracle version >= 11.1, which allows sequences in PL/SQL expressions instead of in SQL, try this:

   v_id := x_seq.nextval;

Instead of this:

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

Or, although I've heard doubts / pitfalls when using ".currval", maybe omit the separate assignment of v_id and only use this code?:

 -- 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;

Sorry I don't have an 11g instance handy now to try this out.

it definitely makes no difference. I use `select into...` in 11 as much as in 9i and 10g. The only benefit from 11+ is being able to explicitly reference it as you've pointed out. — Ben, Feb 17 '12 at 21:41

How could sequence.nextval be null in Oracle?

3 Answers3