Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue with sequence

571956Jan 16 2009 — edited Jan 16 2009
I 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??
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 13 2009
Added on Jan 16 2009
13 comments
464 views