Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06502: PL/SQL: numeric or value error: number precision too large [message #122254] Sat, 04 June 2005 06:59 Go to next message
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 Go to previous messageGo to next message
Frank Naude
Messages: 4420
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.
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #122265 is a reply to message #122257] Sat, 04 June 2005 09:17 Go to previous messageGo to next message
Gurusubramanyam
Messages: 79
Registered: July 2001
Member
Dear Frank,

Thanks for your reply. But I want to know, how to avoid this error in my program? To acheive some of the complex accurate calculations we have to define our datatype like this.can you please provide your advice.

Bye,
G.S
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #122267 is a reply to message #122265] Sat, 04 June 2005 09:30 Go to previous messageGo to next message
Frank Naude
Messages: 4420
Registered: April 1998
Senior Member
In that case, define variable "a" as NUMBER(11,9) instead of NUMBER(9,11).

Best regards.

Frank
Re: ORA-06502: PL/SQL: numeric or value error: number precision too large [message #122270 is a reply to message #122267] Sat, 04 June 2005 10:44 Go to previous message
Gurusubramanyam
Messages: 79
Registered: July 2001
Member
O.K . Thanks

Regards,
G.S
Previous Topic: external library ENCRYPT_LIB name "Encrypt"
Next Topic: Store query result to variable
Goto Forum:
  


Current Time: Tue Sep 23 10:02:43 CDT 2014

Total time taken to generate the page: 0.08496 seconds