Oracle pl/sql error: ORA-06502: PL/SQL: numeric or value error:

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

This error occurs due to an arithmetic, numeric, string, conversion problem.
This normally displayed as ORA-06502: PL/SQL: numeric or value error:This error can happen because of different scenarios.
See below the common cause of this error.
The below give examples explain different scenarios.
1. In the below example we have declared the string ‘msg’ as string with maximum 10 characters.
In the code we assign a big string to ‘msg’. The error retured is ‘PL/SQL: numeric or value error: character string buffer too small’.
Consider the following example

SQL> set serveroutput on;
SQL> declare
msg varchar2(10);
--num number;
begin
msg := 'String with more than 10 characters!';
--num := msg;
dbms_output.put_line(msg);
end;
/
declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 5
SQL>

2. In the below example we tried to assign ‘string’ value to ‘number’ variable. It throws the same error code with different message. ‘ORA-06502: PL/SQL: numeric or value error: character to number conversion error’.
Consider the following example

SQL>
declare
msg varchar2(10);
num number;
begin
msg := 'String';
num := msg;
dbms_output.put_line(msg);
end;
/
declare * ERROR at line 1: ORA-06502:
PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 6
SQL>

Technorati Tags:
, ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

One thought on “Oracle pl/sql error: ORA-06502: PL/SQL: numeric or value error:

  1. Pingback: ORA-01722: invalid number | PL/SQL :ORA-01722 Error | SQL and PLSQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page