posted 3 years ago

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.

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.

Tea Addict, Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer

posted 3 years ago

- 1

See the documentation, namely:

Precision is the total number of preserved digits.

Edit:

Optionally, you can also specify aprecision(total number of digits) andscale(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.
posted 3 years ago
Tea Addict, Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer

Thank you martin , thank you very much.

posted 3 years ago
Tea Addict, Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer

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

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

posted 3 years ago

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

Another rather confusing situation can arise when scale is greater than precision, which means

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: