Data Types - Number Data Types In Oracle

Data types specify type of data within a table column. Oracle has following data types...

Number (P,5)
Char -varchar2(max size)
Date

1) Number[P, S)

P: Precision (total number of digits)

S: Scale (it is used to store fixed, floating point numbers).

syntax :columnname number(p.s)

Example

SQt> create table test (sno number (7, 2))

SQL> insert into test values (12345.67);
SQL> select from test;

Sno

---------------

12345.67

SOL> insert into test values (123456.7)

Error: value larger than specified precision allows for this column.

Note 1: Whenever we are using numbers (P, S) then total number of digits before decimal places up to "P-5" number of digits.

Example: p-s=>7-2=5

After Decimal

SQL> insert into test values (12345.6789);
SQL select from test;

5no

---------------

12345.68

SQL> insert into test values (12345.6725);

SQL> select from test;

Sno

---------------

12345.67

Note 2: Oracle server does not return any error whenever we are using number (p, s) and also if we are
passing more digits after the decimal place then the oracle server internally automatically rounded that number based on a specified "scale".

Number (P):

It is used to store fixed numbers only. Maximum length of the precision is up to "38".

Example

SOL> create table test1 (sno number (7));

SQL> insert into test1 values (99.9);

SQL> select * form test1;

Sno

---

100

SQL> insert into test1 values (99.4);

SQl> select* from test1;

Sno

----

Data Types - Number Data Types In Oracle - Deepak YT