Issue with sequence
571956Jan 16 2009 — edited Jan 16 2009I am running into an integrity constraint issue that I believe has something to do with one of my sequences.
I run my package, it finishes, then I run it again and I get my integrity constraint error. If I log out and log back in, my package completes successfully. Here is the procedure it is erroring out at (I bolded the line it errors out on):
PROCEDURE DATABASE_PROGRESS (v_project IN VARCHAR2, v_table IN VARCHAR2, v_label IN VARCHAR2, v_transfer_id IN NUMBER) IS
BEGIN
SELECT rcdtpump.progress_id.NEXTVAL
INTO v_progress
FROM DUAL ;
IF v_progress IS NULL THEN
v_progress := 1 ;
END IF ;
EXECUTE IMMEDIATE
'INSERT INTO rcdtpump.database_progress(
progress_id,
progress_label,
owner,
table_name,
progress_type,
transfer_id,
rowdate)
VALUES (:1, :2, :3, :4, ''5'', :5, SYSDATE)'
USING v_progress,
v_label,
v_project,
v_table,
v_transfer_id ;
COMMIT ;
END DATABASE_PROGRESS ;
After running it the first time, the max PROGRESS_ID in the DATABASE_PROGRESS table is 17. The second time I run it I output the value of the v_progress variable BEFORE I update it, and the value is 16. It updates and becomes 17, thus causing the integrity constraint error. HOWEVER, when I log out, log back in, the v_progress variable gets assigned correctly.
Any ideas on what is going on here??