 Posted 6/11/2010 12:08:14 PM
|
|
|
|
| ora 06502 character string buffer too small Oracle 10g: 06502, 00000, "PL/SQL: numeric or value error%s"
// *Cause:
// *Action:
Oracle 11g: 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.
|
|
|
Posted 6/11/2010 12:08:51 PM
|
|
|
|
Hi I am getting the following error while doing a insert into a table.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small Can anyone help?
|
|
|
Posted 6/11/2010 12:10:14 PM
|
|
|
|
| You get this ora error ora-06502 when you attempt to assign a value to a PL/SQL variable which is not big enough for it. Run this before you run your PL/SQL
SQL > set serveroutput on buffer 2560000
|
|
|
Posted 6/11/2010 12:10:41 PM
|
|
|
|
| I was able to find the issue and resolve this ora error 6502 .
It was indeed a variable that wasn't large enough to accept the returned value of a query.
|
|
|
Posted 6/11/2010 12:13:03 PM
|
|
|
|
| I got this ora error when i had TOAD generate a sql for me. I tried it manually: SQL> exec dbms_output.put_line(lpad('a',501,'a'))
BEGIN dbms_output.put_line(lpad('a',501,'a')); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
Then I realize that TOAD was defining the parameter to VARCHAR2 (22) which was not enough for it. So, when I changed it to 2000, it ran. Thanks.
|
|
|
Posted 6/11/2010 12:14:18 PM
|
|
|
|
ORA-06502: character string buffer too small HELP!?Hi guys, here's my script:
DECLARE
v_Ccustid Number;
v_Cname Customer.Cust_name%TYPE;
v_Caddress Customer.Cust_address%TYPE;
v_Cphone Customer.Cust_phone%TYPE;
BEGIN
FOR v_Ccustid IN 1..2000
LOOP
v_Cname := 'Name' || TO_CHAR(v_Ccustid);
v_Caddress := 'Address' || TO_CHAR(v_Ccustid);
v_Cphone := to_char (DBMS_RANDOM.VALUE(0000000,9999999));
INSERT INTO Customer
VALUES (v_Ccustid, v_Cname, v_Cphone, v_Caddress);
END LOOP;
Commit;
END;
Whenever I try to execute it, it keeps giving me
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 11
and I'm at a lost as to what I need to do, I've searched on the Internet for a solution but I still can't find one.
By the way, Custid is given value NUMBER(5), if that helps.
|
|
|
Posted 6/11/2010 12:15:54 PM
|
|
|
|
| In your case the ORA-06502 ora error you can do either of the following: Solutions:
1. Update table Customer by increasing the maximum length of the text field that is causing exception - then re-compile depending modules and verify that all your code works fine with longer values (it should if was correctly written and most probably it won't if was incorrectly written)
2. Use shorter strings, example:
DECLARE
v_Ccustid Number;
v_Cname Customer.Cust_name%TYPE;
v_Caddress Customer.Cust_address%TYPE;
v_Cphone Customer.Cust_phone%TYPE; Hope that helps. Basically increase the value just like mentioned in previous posts.
|
|
|
|