Number Datatype

[Mahtab Alam]

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 Vashko]

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]

Thank you martin , thank you very much.

[Mahtab Alam]

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 Vashko]

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:
SQL> create table test2(x number(2, 5)); Table created. SQL> insert into test2 values(1); insert into test2 values(1) * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column SQL> insert into test2 values(0.001); insert into test2 values(0.001) * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column SQL> insert into test2 values(0.0001); 1 row created.