Skip to Main Content
  • Questions
  • CURSOR variables out of scope when procedure triggers Exception block?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tom.

Asked: October 20, 2015 - 11:10 am UTC

Last updated: October 20, 2015 - 2:10 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

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

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

Tom, October 20, 2015 - 2:40 pm UTC

Just as I though, thanks for the speedy reply.
Tom

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library