I'm getting an error in a procedure that has never triggered its exception block before. It seems simple and I think I already know the answer, but the issue is that a cursor REC1 (id, description, some other variables) is opened within the procedure and an exception block WITH OTHERS clause is present. When triggered, it causes and insert into a logging table so we can know which row within the cursor caused the error and backtrack..). It has been failing lately indicating the insert stmt in the exception block which references "REC1.ID".
The code is something like this:
declare
cursor user_notifications is
select id, name, description, sql_stmt
from notification_table;
other variables here....
begin
for rec1 in user_notifications loop
set some variables;
generate and execute some dynamic insert statements here;
end loop;
exception
when others then
insert into notification_error_log (id, created_on)
values (rec1.id, sysdate);
commit;
raise;
end;
The error is:
ORA-00904: "REC1"."ID": invalid identifier
and the line identifier points to the insert in the exception block.
So the question is would the cursor opened in the body of the procedure be out of scope within the exception block? I'm guessing the answer is "Yes, it would be out of scope".
Tom
Yes, it's out of scope. Loop index variables declared are local to the loop:
The cursor FOR LOOP statement implicitly declares its loop index as a %ROWTYPE record variable of the type that its cursor returns. This record is local to the loop and exists only during loop execution. Statements inside the loop can reference the record and its fields. They can reference virtual columns only by aliases, as in Example 6-21.
After declaring the loop index record variable, the FOR LOOP statement opens the specified cursor. With each iteration of the loop, the FOR LOOP statement fetches a row from the result set and stores it in the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if PL/SQL raises an exception.
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS553