ORA-06502: PL/SQL: numeric or value error

I got ” ORA-06502: PL/SQL: numeric or value error ” error in Oracle.

 

ORA-06502: PL/SQL: numeric or value error

 

Details of error are as follows.

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.

 

 

PL/SQL: numeric or value error

This ORA-06502 errors are related with the arithmetic, numeric, string, conversion, or constraint error occurred.

For example; I have created a procedure, it is created.

SQL> CREATE OR REPLACE PROCEDURE Test
AS
id number(3);
BEGIN
id := 'M';
END;
/

Procedure created.

 

But When I execute it, I got the following error. Because id column is number type but we set it ‘M’ Character. So we got the ” ORA-06502: PL/SQL: numeric or value error: character to number conversion error ” as follows.

SQL> execute Test();
BEGIN Test(); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "EXAMPLE.Test", line 5
ORA-06512: at line 1

 

I have created it again with the number column type and set number value.

SQL> CREATE OR REPLACE PROCEDURE Test
AS
id number(2);
BEGIN
id := 63;
END;
/

Procedure created.

 

This time I didn’t get any error as follows.

SQL> execute Test();

PL/SQL procedure successfully completed.

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

See more