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!

invalid identifier error in Bulk Collect

oaksongMar 16 2009 — edited Mar 24 2009
I'm trying to create the following procedure and I'm getting a compile error: PL/SQL: ORA-00904: "CASE_ID": invalid identifier.
----------------------------------------------------------------------------------------------------------------------------
/* update Patient ID matched from case number */
Create or replace procedure update_Case_with_Patient_ID AS

cursor case_new is
select * from v_case_staff_model
;
type v1_type is table of case_new%rowtype; /* create a type for v */
v v1_type; /* identify v as the type */

begin
open case_new;

loop

fetch case_new bulk collect into v limit 1000;

FOR indx IN 1..v.COUNT
loop

Update t_case set patient_id = v.patient_id
where case_id(indx) = t_case.case_id;

end loop;
exit when v%notfound;
end loop;
end;
--------------------------------------------------------------------------------------------------------------------------------------------------------------
case_new, from the view, has two fields; case_id and patient_id. The error is generated in the where clause of the upate on "case_id(indx)".

I've spent the morning looking at various syntax structures and the above seemed to be the closest to what I want.

Suggestions?

tia
Chris
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 21 2009
Added on Mar 16 2009
7 comments
945 views