Itgenpse031: Error on line [...] Variable with name '***' is already declared occurs when 2 loop in same statement

Questions
, ,
1

Good afternoon,
there is as small code deviation issue I found in 22.0.330. I’m not sure since when.
when there are 2 loop statement with the same loop variable, Invantive says it is already declared.
it seems that the exec tries to declare it 2 times, since there are two loops whereas it was not the case in the past
for the time being, the turnaround is to use a different named loop variable in the second loop.

example:

create or replace table mytable@inmemorystorage (v varchar2);
insert into mytable@inmemorystorage (v) values ('value1');
insert into mytable@inmemorystorage (v) values ('value2');
insert into mytable@inmemorystorage (v) values ('value3');
declare
l_store varchar2;
begin
for r in (select * from mytable@inmemorystorage)
loop
  l_store := l_store || r.v
  ;
end loop;
for r in (select * from mytable@inmemorystorage)
loop
  l_store := l_store || r.v
  ;
end loop;
end;

Capture d’écran 2022-08-22 à 18.16.16
Capture d’écran 2022-08-22 à 18.16.163046×1382 232 KB

turnaround

create or replace table mytable@inmemorystorage (v varchar2);
insert into mytable@inmemorystorage (v) values ('value1');
insert into mytable@inmemorystorage (v) values ('value2');
insert into mytable@inmemorystorage (v) values ('value3');
declare
 l_store varchar2;
begin
for r in (select * from mytable@inmemorystorage)
loop
  l_store := l_store || r.v
  ;
end loop;
for f in (select * from mytable@inmemorystorage)
loop
  l_store := l_store || f.v
  ;
end loop;
end;
2

Thanks for your post. You are correct this is the current behavior, which was introduced somewhere this year. There is no solution in the short term to fix this unpreferred behavior.

Please change your scripts as you already have done.

3

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.