Home » SQL & PL/SQL » SQL & PL/SQL » Truncate precision in number datatype oracle (oracle 10g,windows)
Truncate precision in number datatype oracle [message #561243] Thu, 19 July 2012 14:05 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

We have truncated number based on the decimal value. i tried to truncate number based on the precision using cast function. i got an error "value larger than specified precision allowed for this colum".


create table TEST_NUMBER
(id number(4,1));


insert into TEST_NUMBER
values(1234.789888888888);

select *
from TEST_NUMBER;

123.8

select cast(id as number(1,1))
from TEST_NUMBER;

ORA-01438: value larger than specified precision allowed for this column
01438. 00000 -  "value larger than specified precision allowed for this column"
*Cause:    When inserting or updating records, a numeric value was entered
           that exceeded the precision defined for the column.
*Action:   Enter a value that complies with the numeric column's precision,
           or use the MODIFY option with the ALTER TABLE command to expand
           the precision.

i want result like 1.8

Re: Truncate precision in number datatype oracle [message #561247 is a reply to message #561243] Thu, 19 July 2012 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
how or why does 123.8 get transformed to 1.8?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Truncate precision in number datatype oracle [message #561264 is a reply to message #561247] Thu, 19 July 2012 19:42 Go to previous messageGo to next message
Paulie
Messages: 22
Registered: May 2012
Location: Ireland
Junior Member
BlackSwan wrote on Thu, 19 July 2012 21:59
how or why does 123.8 get transformed to 1.8?


Not to mention the transformation of 1234.789888888888 to 123.8!


Paul...
Re: Truncate precision in number datatype oracle [message #561270 is a reply to message #561264] Fri, 20 July 2012 01:03 Go to previous message
_jum
Messages: 490
Registered: February 2008
Senior Member
You find the soultion in the manual NUMBER(p,s):
The precision is the total number of digits and the scale is the number of digits to the right of the decimal point.
So You can't store 123.8 with precision 1, you need at least precision 4:
SELECT CAST (123.78888 AS NUMBER (4, 1)) 
  FROM DUAL;

----------------------------
                       123,8
1 row selected.



Previous Topic: Approach Needed ( Pl/SQl table?)
Next Topic: Compile specific packaged procedure
Goto Forum:
  


Current Time: Sat Aug 30 15:23:54 CDT 2014

Total time taken to generate the page: 0.09558 seconds