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

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.

Hope you are solving this error. Best of luck. For more information, comment in this post with your email id and I will reply back.

Why Function based index

Who can read this: A basic understanding of indexes, updates and their effects on indexes would help better understand the topic of this document. This article is intended to be an introduction to the topic of Function based index.

Who should not read this: This document is meant to be a basic manual for those who would like to learn what are function based indexes and what magic can they do. So the persons who are already aware of this concepts, please do not waste your time. Also this concept is confined to Oracle database. There are no general topics discussed in this article.

Now let's start with the topic straightaway:

What and why a function based index is used? 

Function based indexes were introduced with Oracle 8i. This was the most coveted features that Oracle came up with its 8i version of database. Traditionally there was no such feature that will use an index if your select statement has a function in it. But with this feature it was possible to use functions such as average, sum and still Oracle will use an index. This is a huge performance gain considering that the data handled by such databases were enormous.

So now you might have got a small idea of what a function based index can do. In a nutshell it is a mechanism whereby frequently accessed functions to a table can be created as a function index and oracle will use the index while such a query is fired. Isn't this a huge performance bonus?

A function based index is created just the same way as the normal index is created. Yes it is with the CREATE INDEX statement that the function based index is created. The following example is borrowed from Tom Kyte (see references for the URL):

SQL> create table emp as select * from scott.emp;
Table created.

SQL> update emp set ename = initcap(ename);
14 rows updated.

SQL> commit;
Commit complete.

SQL> create index emp_upper_idx on emp(upper(ename));
Index created.

In the above section of code, first we are creating a table with the same copy as that of scott.emp, and updating the values with InitCaps. Once the data is committed, the index for UPPER function is created. There is no difference in the clause rather than we can use functions here. Now if any query from an application or from the user with case insensitive queries will use this index.

SQL> set autotrace on explain
SQL> select ename, empno, sal from emp where upper(ename) = 'KING';

ENAME           EMPNO        SAL
---------- ---------- ----------
King             7839       5000

This is the execution plan of the select query:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=40)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=40)
   2    1     INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' (NON-UNIQUE) (Cost=1 Card=1)

It is visible that the INDEX (RANGE SCAN) is used in this case rather than a FULL TABLE SCAN.

This feature becomes more powerful when you consider the fact that user-defined functions could be used as indexes to tables. This will make the application work faster. An example of such a feature is available in the URL provided for Kyte's article.

Privileges required for creating a function based index

1. You must have the query rewrite privilege for your schema

2. You must have the global query rewrite privilege for creating in other schemas

3. If the optimizer has to use a function based index, the following parameters must be set:

    a. QUERY_REWRITE_ENABLED=TRUE

    b. QUERY_REWRITE_INTEGRITY=TRUSTED (These are modifyable through ALTER SYSTEM, ALTER SESSION statements and as well through init.ora file)

Where not to use function based index

As it is with index, the same criteria holds good for function based index. 

References:

Ask Tom: http://asktom.oracle.com/tkyte/article1/

Oracle-Base: http://www.oracle-base.com/articles/8i/FunctionBasedIndexes.php