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?
Best Answer
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.