Win a copy of Head First Agile this week in the Agile forum!
programming forums Java Java JSRs Mobile Certification Databases Caching Books Engineering OS Languages Paradigms IDEs Build Tools Frameworks Products This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
Sheriffs:
Saloon Keepers:
Bartenders:

# Number Datatype

Mahtab Alam
Ranch Hand
Posts: 391
1
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.

Martin Vajsar
Sheriff
Posts: 3752
62
• 1
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
Posts: 391
1
Thank you martin , thank you very much.

Mahtab Alam
Ranch Hand
Posts: 391
1
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
Posts: 3752
62
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: