Variable not seen in exception block -- Error PLS-00201

[toddyl]

Hi,

I am using the code below to try and insert into a table, if there is an entry in the table for this data I want to update it. I thought I was clever by trying to do the following but I am getting an error in the exception block saying that qID must be declared.

I have declared duplicate2 as the name of ORA-000001

Any ideas how I can solve this.

Thanks,

toddyl

Code:
BEGIN
FORALL qID IN arr_qROUTE_TYPE_ID.first .. arr_qROUTE_TYPE_ID.last
INSERT INTO MY_TABLE
( COLUMNS

)
VALUES
(
arr_qRTYPE_ID(qID),
arr_qPID(qID),
arr_qIC_ID(qID),
arr_qRD_ID(qID),
arr_qI_ID(qID),
arr_qS_ID(qID),
arr_qUD_TIME(qID),
);
EXCEPTION
WHEN duplicate_data THEN NULL;
WHEN duplicate2 THEN
UPDATE MY_TABLE
SET TOTAL_CSE = TOTAL_CSE + arr_q_CS(qID),
TOTAL_CSA = TOTAL_CSA + arr_q_CSA(qID),
.........
END;
END IF;

 

[LKBrwnDBA]

Did you declare qID as variable in declaration section?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[carp]

And I assume that the dangling END IF is really matched up to an IF that you omitted in your post.
This would be a lot easier to analyze if you would post ALL of the code - what you have posted is syntactically incorrect, and Oracle's compiler can give misleading error messages at times; the real problem could very well be in something that you have omitted.

 

[toddyl]

I think the problem here relates to the use of FORALL.
If I change this to a FOR loop it works.

Thanks.