Posts

Showing posts from October, 2008

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...

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 featur...