Re: Increment an alphabetic variable in a PL/SQL loop?
- From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
- To: norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx
- Date: Thu, 3 Dec 2009 09:35:37 -0600
You *can* use a combination of ASCII and CHR - e.g.
declare
k_ascii_a constant pls_integer := ascii('A');
l_index varchar2(1);
begin
for i in 1 .. 10 loop -- Change 10 for whatever is your limit
l_index := chr(k_ascii_A + i - 1);
dbms_output.put_line('Index is ' || l_index);
end loop;
end;
*But* this is not portable across character sets. I think a better solution
is to declare up front your index variables like this :
declare
k_index_char constant varchar2(26) := 'ABCDEF...Z';
l_index varchar2(1);
begin
for i in 1 .. 10 loop -- Change 10 for whatever is your limit
l_index := substr(k_index_char, i, 1);
dbms_output.put_line('Index is ' || l_index);
end loop;
end;
That way if you change character sets (e.g. to utf8) you are not reliant on
a collating sequence that doesn't match your char set.
Steve
On Thu, Dec 3, 2009 at 9:28 AM, Dunbar, Norman <
norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> David,
>
> >> Hey how can we increment an alphabetic variable in PL/SQL?
> >>
> >> Let's say I have var1 = 'A' for the first pass, but for the
> >> second pass, I want it to autoincrement to var1='B' and
> >> ultimately to 'Z'.
>
> something like :
>
> var1 char(1) := 'A';
>
> ...
>
> var1 := chr(ascii(var1) +1);
> if (var1 > 'Z') then
> -- handle bigger than Z here.
> end if;
>
>
> Cheers,
> Norman.
>
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify the
> sender immediately, delete it and do not copy it to anyone else. We have
> checked this email and its attachments for viruses. But you should still
> check any attachment before opening it. We may have to make this message and
> any reply to it public if asked to under the Freedom of Information Act,
> Data Protection Act or for litigation. Email messages and attachments sent
> to or from any Environment Agency address may also be accessed by someone
> other than the sender or recipient, for business purposes. If we have sent
> you information and you wish to use it please read our terms and conditions
> which you can get by calling us on 08708 506 506. Find out more about the
> Environment Agency at www.environment-agency.gov.uk
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify the
> sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you should
> still check any attachment before opening it.
> We may have to make this message and any reply to it public if asked to
> under the Freedom of Information Act, Data Protection Act or for litigation.
> Email messages and attachments sent to or from any Environment Agency
> address may also be accessed by someone other than the sender or recipient,
> for business purposes.
>
> If we have sent you information and you wish to use it please read our
> terms and conditions which you can get by calling us on 08708 506 506. Find
> out more about the Environment Agency at www.environment-agency.gov.uk
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
