Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> "number precision too large" using POWER(n,m) function, POWER(47.3616, 27.1261) yields: 2.80403309600359E45
TennesseeTony
post Feb 14 2012, 06:24 PM
Post #1


Newbie
*

Group: Members
Posts: 8
Joined: 16-September 11
From: Tennessee
Member No.: 45,906



POWER(47.3616, 27.1261) returns: 2.80403309600359E45 which causes "number precision too large" error in my variable which is defined as NUMBER(20,20).

I've tried ROUNDing and TRUNCing the product, but still get the same long output. The only thing that seems to work is SUBSTR.

Is that the ONLY way to deal with this? It seems like there would be a better way or is there another type I should use these super long decimal results?

SQL> SELECT POWER(47.3616, 27.1261) AS Exp_Val
2 FROM dual;

EXP_VAL
----------
2.8040E+45

SQL>
SQL> SELECT ROUND(POWER(47.3616, 27.1261),4) AS Exp_Val
2 FROM dual;

EXP_VAL
----------
2.8040E+45

SQL>
SQL> SELECT TRUNC(POWER(47.3616, 27.1261)) AS Exp_Val
2 FROM dual;

EXP_VAL
----------
2.8040E+45

SQL> SELECT SUBSTR(POWER(47.3616, 27.1261),1,6) AS Exp_Val
2 FROM dual;

EXP_VA
------
2.8040

SQL>

Thank you for any input!
Tony
Go to the top of the page
 
+Quote Post
burleson
post Feb 15 2012, 11:10 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,088
Joined: 26-January 04
Member No.: 13



Hi Tony,

>> returns: 2.80403309600359E45

That's a huge number!

>> number precision too large

Are you getting the PLS-01907 error?

PLS-01907: number precision too large

Cause: The number begin copied or assigned had too many digits to the left of the decimal and did not fit into its destination. In other words, there is a number precision mismatch. This error may also occur if, for example, an attempt is made to assign a character string to a number, as demonstrated below. a varchar2(4); b number(1,1); a := "10.1"; b := a;

Action: First, check explicit number precision value mismatches. If none are found, then consider implicit conversions of other types to numbers, including conversions performed during binds.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
TennesseeTony
post Feb 16 2012, 09:27 AM
Post #3


Newbie
*

Group: Members
Posts: 8
Joined: 16-September 11
From: Tennessee
Member No.: 45,906



I get "ORA-06502: PL/SQL: numeric or value error- ERROR: -6502"

The code I am testing is here:


DECLARE
TV_VAL NUMBER(30,30);
BEGIN
TV_VAL:= POWER(47.3616, 27.1261);
END;

Are there any other data types other than NUMBER I should try?

Thanks!
Tony
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 17th April 2014 - 07:12 PM