PL/SQL: numeric or value error%s error converting character to number

Question

I have a problem with a pl/sql anonymous block. Although I can show the variables that get with select query, I get a casting error if I use it in update sentence.

My code looks like this:

set serveroutput on

DECLARE

var1 scheme.table.column1%type; /* number[8,0] */
var2 scheme.table.column2%type; /* number[8,2] */

BEGIN

/* return simple value */
select t.column1, t.column2
into var1, var2
from scheme.table t
where <condition>

DBMS_OUTPUT.PUT_LINE('Var1 is: ' || var1 || ' Var2 is:' || var2); /* fine!!*/

update scheme.table
set scheme.table.column1 = var1 + 1
where scheme.table.column2 = var2; /* that is line error*/

END;

/*
ORA-06502: PL/SQL: error : error de conversión de carácter a número numérico o de valor
ORA-06512: en línea 58
06502. 00000 -  "PL/SQL: numeric or value error%s"
*/

Can somenone help me?

what is the value of var1 and var2 that gives you an error?

shareef
– shareef

2012-05-16 11:17:36 +00:00
Commented May 16, 2012 at 11:17 — shareef
– shareef, Commented May 16, 2012 at 11:17

Rene · Accepted Answer · 2012-05-16 11:17:54Z

1

Could it be that var1+1 results in a number larger than number[8,0] for a record where column2=val2?

You query using "condition" but update using "column2=var2". So potentially you update many more rows than you may think you do.

answered May 16, 2012 at 11:17

Rene

10.6k5 gold badges37 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dvdsantana Over a year ago

Thank you Rene. I finally solved it. Code is correct, but no sqldeveloper. I compile same code sucessfully, so i was looking in wrong place.

shareef · Accepted Answer · 2012-05-16 11:19:34Z

0

ORA-06502: PL/SQL: numeric or value error string 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.

i think you error is the second choice.

reference to you error ORA-06502

answered May 16, 2012 at 11:19

shareef

9,64116 gold badges64 silver badges94 bronze badges

2 Comments

shareef Over a year ago

@user1398399 please provide the create table script especially its constraints

dvdsantana Over a year ago

Thank you shareef. I finally solved it. Code is correct, but no sqldeveloper. I compile same code sucessfully, so i was looking in wrong place.

dvdsantana · Accepted Answer · 2012-05-22 06:05:56Z

0

Code is correct, problem with compiler, sqldeveloper.

answered May 22, 2012 at 6:05

dvdsantana

331 gold badge2 silver badges6 bronze badges

1 Comment

shareef Over a year ago

WHAT was the problem please specify ?

Collectives™ on Stack Overflow

PL/SQL: numeric or value error%s error converting character to number

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related