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

The docs note this on the ORA-06502 error:

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.

More Information by Anantha:

This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.

Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field.

For example:

declare
test varchar2(1);
begin
test := 'I am here';
end;

The above block gives the error:
ORA-06502: PL/SQL: numeric or value error: character buffer too small
ORA-06512: at line 4


Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning.

Now consider the following example:

declare
test number(2);
begin
test := 100;
end;

The above block gives this error:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

So now you know more about this frequently occuring error.

Some tips to avoid this error:
1. If you are assigning some values from a table to a variable always use the %TYPE declaration.
For Ex:
declare
test my_table.my_field%TYPE;
begin
select my_field from my_table where id=1;
end;

The above declaration methodology is a very efficient one in handling ORA-06502 error.

2. If you are not assigning the values from a table, but rather from some calculation, then use proper validation methodology.
For Ex:
declare
sum_value number(2);
begin
sum_value := 10 + 90;
exception
when value_error then
dbms_output.put_line('Raise your error here');
end;

3. If you are concatenating two strings together also use the above WHEN VALUE_ERROR in exception block to validate your data.
4. It is better method to put a value assigning code inside a seperate BEGIN END block and validate it using EXCEPTION block.

ORA-04031: Unable to allocate %s bytes of shared memory

ORA-04031: Unable to allocate %s bytes of shared memory

 

Solutions:

 

alter system set shared_pool_size=100M; -- size you have to decide based

alter system set large_pool_size=100M;   -- on sga_max_size

 

Oracle limits the increase of size up to SGA_MAX_SIZE parameter defined in the initialization parameter file. SGA memory can not be increase beyond SGA_MAX_SIZE. If SGA_MAX_SIZE parameter is not enough for increasing the memory of dynamic parameters, you will get a ORA-00384 error. In this case either you have to increase the SGA_MAX_SIZE parameter or decrease the memory of dynamic parameter.

 

alter system set db_cache_size=135m;

 

alter system set db_cache_size=135m

 

*

 

ERROR at line 1:

 

ORA-02097: parameter cannot be modified because specified value is invalid

 

ORA-00384: Insufficient memory to grow cache

 

 

alter system set  SGA_MAX_SIZE=150M scope=spfile;

 

System altered.

 

The above command change the  limit of max size  to 150M. You have to shutdown and restart the system to make this effect. As of Oracle 9.0.1.1.1, people mostly get ORA-03113: end-of-file on communication channel, when they  startup the database after shutdown immediate. The best solution for this is to exit from sqlplus and login again.

 

The following SQL query will help you to calculate the approximate size of SGA:

 

select SUM(VALUE)+(1024*1024) from v$parameter where name in (

 'db_16k_cache_size','db_2k_cache_size','db_32k_cache_size',

  'db_4k_cache_size','db_8k_cache_size','db_cache_size',

  'db_keep_cache_size','db_recycle_cache_size',

  'java_pool_size','large_pool_size',

  'shared_pool_size','log_buffer');

 

SUM(VALUE)+(1024*1024)

----------------------

             156762112

 

show parameter cache

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------

db_16k_cache_size                    big integer 0

db_2k_cache_size                     big integer 0

db_32k_cache_size                    big integer 0

db_4k_cache_size                     big integer 0

db_8k_cache_size                     big integer 0

db_cache_advice                      string      OFF

db_cache_size                        big integer 33554432

 

DB_CACHE_SIZE is a replacement of DB_BLOCK_BUFFERS in the older version of oracle 8i and before. The other parameters db_8k_cache_size, db_4k_cache_size, db_32k_cache_size, db_2k_cache_size, db_16k_cache_size have the initial default value "0". You can change values of these parameters dynamically.

 

To configure, the multiple block size feature,  alter the system as : -

 

Syntax :- ALTER SYSTEM SET <PARAMETER NAME> = < NEW SIZE> [M|K]

 

Let us say your block size is 4K and you want to configure database for creating a tablespace of block size 4K and 8K respectively.

 

For tablespace of blocksize 4K, alter the database as

 

ALTER SYSTEM SET DB_2K_CACHE_SIZE = 4M;

 

System altered.

 

For Tablespace of blocksize 8K, alter the database as

 

ALTER SYSTEM SET DB_8K_CACHE_SIZE = 8M;

 

System altered.

 

ALTER SYSTEM SET DB_8K_CACHE_SIZE = 50M;

 

System altered.


Query to find out queries running in database

To find out which queries are running in database, login as SYS and issue the following query:
 
SELECT
a.USERNAME, a.STATUS, b.sql_text
FROM V$SESSION a INNER JOIN V$SQLAREA b
ON a.SQL_ADDRESS= b.ADDRESS;
 
V$SESSION view lists session information for each current session.
 
To see field-wise description for V$SESSION follow the link below:

V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

To see field-wise description for V$SQLAREA follow the link below:

FRM-40502: ORACLE error: unable to read list of values

This is what Oracle has to say about this error:
---

FRM-40502: ORACLE error: unable to read list of values.

Cause:  A fatal error occurred while trying to read a list of values.

Action:  Contact your DBA or an Oracle support representative.

Level:  >25

Type:  Error

---

I had not encountered this error till recent times. But it was today I had encountered this error. I was scratching my head as to what caused this error.

I was working with a form with 6i release, converted to 10g. In that while clicking F9 for LOV in one of the fields, I encountered this error. I started debugging the form, starting from Field, LOV, Record Group. There were no problems at first sight.

I copied the SQL query from the record group and ran it in SQL*Plus, there was no problems. The query was using some bind variables from inside the form. I had modified these fields with values.

I then tried to run exactly the same where clause of the query (I commented out the bind variables) and ran the form. Bingo! It worked and the FRM-4502 error suddenly is gone.

I then tried to run the previous query (with bind variables), now also the error did not came.

I suppose this error is due to the ID of the field with which Forms works rather than the name.

It made a super note for me today...

Hope if you are in same mood, use this logic to solve your problem.

Regards
Anantha