| ORA-06502: PL/SQL: numeric or value error: number precision too large [message #122254] |
Sat, 04 June 2005 06:59  |
Gurusubramanyam
Messages: 79
Registered: July 2001
|
Member |
|
|
Hi,
I have a doubt in PL/SQL basics. Experts can help me out.
I am defining a number data type as number(9,11).In the following scenario when I am assigning a value, I am getting the error.
ORA-06502: PL/SQL: numeric or value error: number precision too large
What is the reason?
Bye,
G.S
------------------------------------------------------------
SQL> declare
2 a number(9,11);
3 begin
4
5 a := 0.01;
6 dbms_output.put_line(a);
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 5
--------------------------------------------------------------
SQL> ed
Wrote file afiedt.buf
1 declare
2 a number(9,11);
3 begin
4 a := 0.001;
5 dbms_output.put_line(a);
6* end;
SQL> /
.001
--------------------------------------------------------------
|
|
|
|
| Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #122257 is a reply to message #122254] |
Sat, 04 June 2005 07:42  |
Frank Naude
Messages: 4579
Registered: April 1998
|
Senior Member |
|
|
Hi,
This is documented behaviour: 11-9=2, so you need at least 2 zeros after the decimal point.
As per the Oracle SQL References Guide:
"You can specify a scale that is greater than precision, although it is uncommon. In this case, the precision specifies the maximum number of digits to the right of the decimal point. As with all number datatypes, if the value exceeds the precision, then Oracle returns an error message. If the value exceeds the scale, then Oracle rounds the value. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point."
Best regards.
Frank.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
