Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajendra.

Asked: January 21, 2017 - 7:05 am UTC

Last updated: January 23, 2017 - 12:56 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

Below is the code structure of what i am trying to do

declare
lowerl number:= 1;
upperl number:= 3;
num varchar2(10);
begin
for i in lowerl..upperl
loop
num:=num||to_char(lowerl);
if i=3 then upperl:=5;
end if;
end loop;
message(num);
end;

The output is 111 but what i am expecting is 1111 . Is my understanding wrong? Because in the above code we are overwriting the For loop max value upper1 to 5 when it reaches 3.

Please help me in this regard

Thanks
Rajendra

and Connor said...

Loops dont work like that. The value of the upper limit is read *once* at the start of the loop execution, eg

SQL> set serverout on
SQL> declare
  2  lowerl number:= 1;
  3  upperl number:= 3;
  4  num varchar2(10);
  5  begin
  6  for i in lowerl..upperl
  7  loop
  8    num:=num||to_char(lowerl);
  9    if i=3 then upperl:=500;
 10    end if;
 11  end loop;
 12  dbms_output.put_line(num);
 13  end;
 14
 15  /
111

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> declare
  2  lowerl number:= 1;
  3  upperl number:= 3;
  4  num varchar2(10);
  5  begin
  6  for i in lowerl..upperl
  7  loop
  8    num:=num||to_char(lowerl);
  9    if i=3 then upperl:=-10;
 10    end if;
 11  end loop;
 12  dbms_output.put_line(num);
 13  end;
 14
 15  /
111


You can workaround it by taking control of the loop variable yourself

SQL> set serverout on
SQL> declare
  2  lowerl number:= 1;
  3  upperl number:= 3;
  4  num varchar2(10);
  5  i pls_integer := 0;
  6  begin
  7  loop
  8    i := i + 1;
  9    num:=num||to_char(lowerl);
 10    if i=3 then
 11      upperl:=5;
 12    end if;
 13    exit when i = upperl;
 14  end loop;
 15  dbms_output.put_line(num);
 16  end;
 17  /
11111

PL/SQL procedure successfully completed.




Rating

  (3 ratings)

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

Comments

The solution doesnot give correct output, and can be done in easy way

Khushi, January 22, 2017 - 6:11 am UTC

The solution which AskTom Team provided doesnot give correct output, and can be done in more easy way.

The q is asked to get output 1111 but by your coding, its coming 11111 and moreover it can be done in much more easier way. as per my understanding, please find below my solution-

declare
lowerl number:= 1;
upperl number:= 4;
num varchar2(10);
begin
for i in lowerl..upperl
loop
num:=num||to_char(lowerl);
--No need of this now if i=3 then upperl:=5;
--end if;
end loop;
message(num);
end;


Thanks,
Please let me know if I am wrong.
Connor McDonald
January 23, 2017 - 12:56 am UTC

At AskTom, we're trying to just go "here's something you can just cut/paste" but explaining the 'why'.

We're interpreting the question as "how do I alter the upper limit" of a loop construct whilst in the loop, as opposed to "how do I get a series of consecutive 1's"


Rajendra Kalepu, January 22, 2017 - 6:24 am UTC

Thank you very much Tom.

Rajendra Kalepu, January 22, 2017 - 6:24 am UTC

Thank you very much Tom.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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