File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Number Datatype Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Number Datatype" Watch "Number Datatype" New topic
Author

Number Datatype

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 222

create table num(price number(2)); //I have specified precision to be 2 So if I try to insert a number of precision more than 2 it should result in error

insert into num values(4.56); //Is

But this statement executed successfully

select price from num;
PRICE
----------
5

-------------------------------------------------------------------------------------------------------------------------
create table num2(price number(3,2));
insert into num2 values(10.45);
//Error Value larger than specified precision allowed for this column

Can you tell What I have misunderstood.


Oracle Java Programmer , Oracle SQL Expert
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

See the documentation, namely:
Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the decimal point)


Precision is the total number of preserved digits. NUMBER(3,2) means that you want to store three digits in total, two of them to the right of the decimal point. It leaves space for only one digit to the left of the decimal point. 10.45 (as well as 10) has two digits to the left of the decimal point, hence the error - the largest number you can store in NUMBER(3,2) is 9.99.

Edit: NUMBER(2) is equal to NUMBER(2,0), meaning that there are no decimal digits. The value 4.56 in your example got rounded off to 5, which is less than the allowed precision (two digits), as Oracle rounds off digits that exceed scale, so you don't get error. Only exceeding precision causes an error.
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 222

Thank you martin , thank you very much.
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 222

I created a table as create table alex(marks number(4,-1))

and I executed this insert statement

insert into alex values(12345.23);
//1 row inserted
//But since precision was 4 I was expecting above insert statement to fail.

When i select it shows
MARKS
---------
12350
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Mahtab Alam wrote:But since precision was 4 I was expecting above insert statement to fail.

The trick is that you specified a negative scale. Negative scale means that some digits to the left of decimal points will always be zero. These digits do not count towards precision - they don't hold any useful information.

For a given scale and precision of Oracle number, the maximum number of digits to the left of decimal point can be expressed as precision - scale. So, negative scale allows for more digits to the left of the decimal point, but only some of them (the amount given by precision) will keep any useful information. Writing the numbers in scientific notation would help you to understand what "precision" actually means.

Another rather confusing situation can arise when scale is greater than precision, which means precision - scale is negative. In this case, there will be at least scale - precision zero digits to the right of the decimal point. Consider:
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Number Datatype
 
Similar Threads
Problem Using toString method from a different class
Need help with Overloading Methods
Decimal precision and scale
problems with double value
Insert with a select on the same table.