ORA-06502 PLSQL numeric or value error Solution
Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article.
ORA-06502 Cause
The cause of the "ORA-06502 PL/SQL numeric or value error" can be one of many things:
- A value is being assigned to a numeric variable, but the value is larger than what the variable can handle.
- A non-numeric value is being assigned to a numeric variable.
- A value of NULL is being assigned to a variable which has a NOT NULL constraint.
Let's take a look at the solutions for each of these causes.
ORA-06502 Solution
The solution for this error will depend on the cause.
Let's see an example of each of the three causes mentioned above.
Solution 1: Value Larger than Variable (Number Precision Too Large)
In this example, we have some code that is setting a numeric variable to a value which is larger than what can be stored.
Let's create this procedure which declares and then sets a variable:
1CREATE OR REPLACE PROCEDURE TestLargeNumber
2AS
3 testNumber NUMBER(3);
4BEGIN
5 testNumber := 4321;
6END;
If we compile it, it compiles with no errors.
1Procedure TESTLARGENUMBER compiled
Now, let's run the procedure.
1EXEC TestLargeNumber;
We get an error:
1Error starting at line : 8 in command -
2EXEC TestLargeNumber
3Error report -
4ORA-06502: PL/SQL: numeric or value error: number precision too large
5ORA-06512: at "SYSTEM.TESTLARGENUMBER", line 5
6ORA-06512: at line 1
706502. 00000 - "PL/SQL: numeric or value error%s"
8*Cause: An arithmetic, numeric, string, conversion, or constraint error
9 occurred. For example, this error occurs if an attempt is made to
10 assign the value NULL to a variable declared NOT NULL, or if an
11 attempt is made to assign an integer larger than 99 to a variable
12 declared NUMBER(2).
13*Action: Change the data, how it is manipulated, or how it is declared so
14 that values do not violate constraints.
The error we've gotten is "ORA-06502: PL/SQL: numeric or value error: number precision too large". It also includes an ORA-06512, but that error just mentions the next line the code is run from, as explained in this article on ORA-06512.
This is because our variable testNumber can only hold 3 digits, because it was declared as a NUMBER(3). But, the value we're setting it to a few lines later is 4 digit long (4321).
So, the value is too large for the variable.
To resolve it, increase the size of your variable, or manipulate your value to fit the size of the variable (if possible).
In our example , we can change the size of the variable.
1CREATE OR REPLACE PROCEDURE TestLargeNumber
2AS
3 testNumber NUMBER(4);
4BEGIN
5 testNumber := 4321;
6END;
1Procedure TESTLARGENUMBER compiled
Now, let's run the procedure.
1EXEC TestLargeNumber;
1PL/SQL procedure successfully completed.
The procedure runs successfully. We don't get any output (because we didn't code any in), but there are no errors.
Read more on the Oracle data types here.
While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Solution 2: Non-Numeric Value
Another way to find and resolve this error is by ensuring you're not setting a numeric variable to a non-numeric value.
For example, take a look at this function.
1CREATE OR REPLACE PROCEDURE TestNonNumeric
2AS
3 testNumber NUMBER(4);
4BEGIN
5 testNumber := 'Yes';
6END;
1Procedure TESTNONNUMERIC compiled
The procedure compiles successfully. Now, let's fun the function.
1EXEC TestNonNumeric;
1Error starting at line : 8 in command -
2EXEC TestNonNumeric
3Error report -
4ORA-06502: PL/SQL: numeric or value error: character to number conversion error
5ORA-06512: at "SYSTEM.TESTNONNUMERIC", line 5
6ORA-06512: at line 1
706502. 00000 - "PL/SQL: numeric or value error%s"
8*Cause: An arithmetic, numeric, string, conversion, or constraint error
9 occurred. For example, this error occurs if an attempt is made to
10 assign the value NULL to a variable declared NOT NULL, or if an
11 attempt is made to assign an integer larger than 99 to a variable
12 declared NUMBER(2).
13*Action: Change the data, how it is manipulated, or how it is declared so
14 that values do not violate constraints.
The error we get is "ORA-06502: PL/SQL: numeric or value error: character to number conversion error".
This happens because our variable testNumber is set to a NUMBER, but a few lines later, we're setting it to a string value which cannot be converted to a number
To resolve this error:
- Ensure the value coming in is a number and not a string.
- Convert your string to a number using TO_NUMBER (the conversion might happen implicitly but this may help).
- Convert your string to the ASCII code that represents the string using the ASCII function.
- Change the data type of your variable (but check that your code is getting the right value first).
The solution you use will depend on your requirements.
Solution 3: NOT NULL Variable
This error can appear if you try to set a NULL value to a NOT NULL variable.
Let's take a look at this code here:
1CREATE OR REPLACE PROCEDURE TestNonNull
2AS
3 testNumber NUMBER(4) NOT NULL := 10;
4 nullValue NUMBER(4) := NULL;
5BEGIN
6 testNumber := nullValue;
7END;
Procedure TESTNONNULL compiled
Now, the reason we're using a variable to store NULL and not just setting testNumber to NULL is because we get a different error in that case. Besides, it's probably more likely that your NULL value will come from another system or a database table, rather than a hard-coded NULL value.
Let's run this function now.
1Error starting at line : 9 in command -
2EXEC TestNonNull
3Error report -
4ORA-06502: PL/SQL: numeric or value error
5ORA-06512: at "SYSTEM.TESTNONNULL", line 6
6ORA-06512: at line 1
706502. 00000 - "PL/SQL: numeric or value error%s"
8*Cause: An arithmetic, numeric, string, conversion, or constraint error
9 occurred. For example, this error occurs if an attempt is made to
10 assign the value NULL to a variable declared NOT NULL, or if an
11 attempt is made to assign an integer larger than 99 to a variable
12 declared NUMBER(2).
13*Action: Change the data, how it is manipulated, or how it is declared so
14 that values do not violate constraints.
We get the ORA-06502 error.
This error message doesn't give us much more information. But, we can look at the code on line 6, as indicated by the message. We can see we have a variable that has a NOT NULL constraint, and the variable is NULL.
To be sure, we can output some text in our demo when it is null.
1CREATE OR REPLACE PROCEDURE TestNonNull
2AS
3 testNumber NUMBER(4) NOT NULL := 10;
4 nullValue NUMBER(4) := NULL;
5BEGIN
6 IF (nullValue IS NULL) THEN
7 dbms_output.put_line('Value is null!');
8 ELSE
9 testNumber := nullValue;
10 END IF;
11END;
Now let's call the procedure.
1EXEC TestNonNull;
1Value is null!
The output shows the text message, indicating the value is null.
ORA-06502 character string buffer too small
This version of the error can occur if you set a character variable to a value larger than what it can hold.
When you declare character variables (CHAR, VARCHAR2, for example), you need to specify the maximum size of the value. If a value is assigned to this variable which is larger than that size, then this error will occur.
For example:
1DECLARE
2 charValue VARCHAR2(5);
3BEGIN
4 charValue := 'ABCDEF';
5END;
If I compile this code, I get an error:
1ORA-06502: PL/SQL: numeric or value error: character string buffer too small
2ORA-06512: at line 4
This happens because the variable is 5 characters long, and I'm setting it to a value which is 6 characters long.
You could also get this error when using CHAR data types.
1DECLARE
2 charValue CHAR(5);
3BEGIN
4 charValue := 'A';
5 charValue := charValue || 'B';
6END;
1ORA-06502: PL/SQL: numeric or value error: character string buffer too small
2ORA-06512: at line 5
This error happens because the CHAR data type uses the maximum number of characters. It has stored the value of A and added 4 space characters, up until its maximum value of 5.
When you try to concatenate a value of B to it, the resulting value is 'A B', which is 6 characters.
To resolve this, use a VARCHAR2 variable instead of a CHAR, and ensure the maximum size is enough for you.
ORA-06502: pl/sql: numeric or value error: null index table key value
Sometimes you might get this error message with the ORA-06502 error:
1ORA-06502: pl/sql: numeric or value error: null index table key value
This means that either:
- Your index variable is not getting initialized, or
- Your index variable is getting set to NULL somewhere in the code.
Check your code to see that neither of these two situations are happening.
ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind
You might also get this specific error message:
1ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind
This is caused by an attempt to SELECT, UPDATE, or INSERT data into a table using a PL/SQL type where a column does not have the same scale as the column in the table.
For example, you may have declared a variable in PL/SQL to be VARCHAR2(100), but your table is only a VARCHAR2(50) field. You may get this error then.
You may also get this error because some data types in PL/SQL have different lengths in SQL.
To resolve this, declare your variables as the same type as the SQL table:
1type t_yourcol is table of yourtable.yourcol%TYPE;
So, that's how you resolve the ORA-06502 error.
While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: