ORA-06502 PL SQL numeric or value error character string buffer too small

0 votes

I experimented with the following code in many ways, such as by removing the while or the if, but whenever I combine the two (if and while), I consistently receive the error at the end.

undefine numero
set serveroutput on
accept numero prompt 'Type # between 100 and 999: '
declare
   i number:=1;
   a char(25);
   b char(1);
   c varchar2(10);
   d number;
begin
   c := №
   d := length(c);
   b := substr(c, i, 1);
   while i <= d loop
     if b = '1' then
       a:= a||'one ';
     end if;
     i := i+1;
   end loop;
   dbms_output.put_line('The number is '||a);
end;
/

ERROR:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 13
06502. 00000 -  "PL/SQL: numeric or value error%s"

FIXED by changing how I declared the variable "a" to:

a varchar2(2000);

*Notice that here, the significant change is to use VARCHAR2 instead of CHAR (not the bigger length). According to @user272735 's answer, that's the key.

Aug 22, 2022 in Database by Kithuzzz
• 38,010 points
5,769 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
0 answers

SQL Error: ORA-00942 table or view does not exist

I'm using SQL Developer, and after creating ...READ MORE

Sep 4, 2022 in Database by Kithuzzz
• 38,010 points
925 views
0 votes
0 answers

SQL Error: ORA-00913: too many values

The table name, column names, datatype, and ...READ MORE

Sep 5, 2022 in Database by Kithuzzz
• 38,010 points
1,353 views
0 votes
1 answer

SQL Error: ORA-01861: literal does not match format string 01861

Try replacing the string literal for date ...READ MORE

answered Sep 13, 2022 in Database by narikkadan
• 63,700 points
12,874 views
0 votes
1 answer

SQL Error: ORA-00922: missing or invalid option

The lack of an underscore between "chartered" ...READ MORE

answered Sep 15, 2022 in Database by narikkadan
• 63,700 points
1,871 views
0 votes
1 answer

SQL Server Error : String or binary data would be truncated

You're trying to write more data than ...READ MORE

answered Sep 15, 2022 in Database by narikkadan
• 63,700 points
5,539 views
0 votes
0 answers

PL/SQL ORA-01422: exact fetch returns more than requested number of rows

I am getting this error: DECLARE * ERROR at line ...READ MORE

Aug 19, 2022 in Database by Kithuzzz
• 38,010 points
863 views
0 votes
1 answer

Please name some online websites to compile and run PL/SQL?

For executing Oracle SQL queries and PL/SQL ...READ MORE

answered Feb 11, 2022 in Database by Neha
• 9,060 points
1,236 views
0 votes
1 answer

Number of rows affected by an UPDATE in PL/SQL

You can try using the following query: SELECT ...READ MORE

answered Feb 14, 2022 in Database by Vaani
• 7,060 points
1,073 views
0 votes
1 answer

SQL error "ORA-01722: invalid number"

When an effort is made to convert ...READ MORE

answered Feb 15, 2022 in Database by Neha
• 9,060 points
2,760 views
0 votes
1 answer

Oracle PL/SQL - ORA-01403 "No data found" when using "SELECT INTO"

Just populate the field directly, using ordering ...READ MORE

answered Sep 18, 2022 in Database by narikkadan
• 63,700 points
4,577 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP