Hi Tom,
I would like to show many characters ascii values and I use this sql below
set serveroutput on
declare
c varchar2 (100);
a number (3);
begin
for i in ( select 'abcde' from dual
) loop
for j in 1..length('abcde')
loop
c := substr('abcde',j,1);
a := ascii (c);
dbms_output.put_line (j || '|' || c || '| ' || a);
end loop;
dbms_output.put_line ('----------------------------------');
end loop;
end;
/The result is following below.
1|a| 97
2|b| 98
3|c| 99
4|d| 100
5|e| 101
----------------------------------
declare
c varchar2 (100);
a number (3);
begin
for i in ( select 'abc??de' from dual
) loop
for j in 1..length('abc??de')
loop
c := substr('abc??de',j,1);
a := ascii (c);
dbms_output.put_line (j || '|' || c || '| ' || a);
end loop;
dbms_output.put_line ('----------------------------------');
end loop;
end;
/But When I would like show some special characters in the string with the same code, I got errors. My question is how can I show special character in decimal value, any ideas?
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 12
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.Any ideas for this?
Kind Regards,
Derek
You get the "number precision too large" error when you try and stuff more digits into your number variable than it supports:
declare
n number(1);
begin
n := 12;
end;
/
ORA-06502: PL/SQL: numeric or value error: number precision too large
So presumably the character you're trying to convert has an ASCII value > 999.
You could fix this by declaring the A size variable as a large number:
declare
c varchar2 (100);
a number (10);
Or using SQL instead ;)
with strings as (
select 'abcde' str from dual
)
select substr ( str, l, 1 ) chr,
ascii (
substr ( str, l, 1 )
) ascii#
from strings,
lateral (
select level l from dual
connect by level <= length ( str )
);
CHR ASCII#
a 97
b 98
c 99
d 100
e 101