SQL & PL/SQL

If you didnt specify the precision and scale ,then it will store as the same value.

Check the below link:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm

 
HR2@orcl>CREATE TABLE employees
(
salary NUMBER
);      2    3    4
Table created.
HR2@orcl>
HR2@orcl>
HR2@orcl>insert into employees values(188.56);
1 row created.
HR2@orcl>select * from employees;
    SALARY
----------
    188.56

 
 
 
HR2@orcl>CREATE TABLE employees_1
  2  (
  3  salary NUMBER(3,0)
  4  );
Table created.
HR2@orcl>insert into employees_1 values(188.56);
1 row created.
HR2@orcl>select * from employees_1;
    SALARY
----------
       189

If a precision is not specified, the column stores values as given. If no scale
is specified, the scale is zero.



Input Data
Specified As
Stored As





7,456,123.89



NUMBER



7456123.89

This is provided in Oracle docs.

Oracle Data Types

hi,

refer

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

so precision and scale are maxed out

Native Datatypes

----

Ramin Hashimzade

If no scale
 
is specified, the scale is zero.
 

Exactly.

But Stored As - 7,456,123.89 has a scale of 2 when default scale is 0!!!!!

If you have mentioned precision but not scale, what you have mentioned is correct. But if precision and scale are not mentioned, then it inserts value as it is. I think Kark has already given you the explanation. Please see this.

orcl>CREATE TABLE TAB_P(ID NUMBER);
Table created.
orcl>INSERT INTO TAB_P(ID) VALUES(188.56);
1 row created.
orcl>SELECT * FROM TAB_P;
        ID
----------
    188.56
orcl>CREATE TABLE TAB_PS(ID NUMBER(3,0));
Table created.
orcl>INSERT INTO TAB_PS(ID) VALUES(188.56);
1 row created.
orcl>SELECT * FROM TAB_PS;
        ID
----------
       189

Hope this helps

Ora-aff wrote:

 
 

If no scale
   
is specified, the scale is zero.
   

 
 
Exactly.
But Stored As - 7,456,123.89 has a scale of 2 when default scale is 0!!!!!

Not when you specify just NUMBER. There is no precision or scale - it is stored as given.

If you specify NUMBER(9), for example, then the scale is defaulted to 0.

If you didnt specify the precision and scale,then it will insert whatever you specified. (will not consider both precision and scale)

If you specify precision and not scale , then the scale is zero.

EXAMPLE:  VAR1 NUMBER(10)

See the example which I have given above and also below:

SQL>CREATE TABLE employees_2
(
salary NUMBER(3)
);
Table created.
SQL>insert into employees_2 values(188.56);
1 row created.
SQL>select * from employees_2;
 
    SALARY
----------
       189

Does it mean a table column declared as just NUMBER will store virtually any number? 

In Oracle doc it is mentioned: "The absence of precision and scale designators specifies the maximum range and precision for an Oracle number."

If the above line means the maximum precision then a table column declared as just NUMBER should not accept values larger than precision 38.

Ora-aff wrote:

Does it mean a table column declared as just NUMBER will store virtually any number?  


In Oracle doc it is mentioned: "The absence of precision and scale designators specifies the maximum range and precision for an Oracle number." 


If the above line means the maximum precision then a table column declared as just NUMBER should not accept values larger than precision 38.

No, it just means it can store it up to precision of 38.  From the docs:

NUMBER Datatype

The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision.

The following numbers can be stored in a NUMBER column:

• Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10¹²⁵ with up to 38 significant digits
• Negative numbers from -1 x 10^-130 to 9.99...99 x 10¹²⁵ with up to 38 significant digits
• Zero
• Positive and negative infinity (generated only by importing from an Oracle Version 5 database)

It is already given in the Oracle docs as range of number datatype (if not mentioned any precision is).

Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits

So if you are not mentioning any precision, it will accept whatever values you provide, if it is in the above mentioned range. But if you specify precision, it will error out if the input value exceeds the specified precision. See this.

-- CREATE TABLE WITHOUT SPECIFYING PRECISION
orcl>CREATE TABLE TAB_P (ID NUMBER);
Table created.

--- INSERT DIGITS OF LENGTH 38.
orcl>INSERT INTO TAB_P(ID) VALUES(12345678901234567890123456789012345678);
1 row created.

--- INSERT DIGITS OF LENGTH 39. (1 more than the default range allowed)
orcl>INSERT INTO TAB_P(ID) VALUES(123456789012345678901234567890123456789);
1 row created.

SYS@orcl>SELECT LENGTH(ID) FROM TAB_P;
LENGTH(ID)
----------
        38
        39

Now try with precision specification.

orcl>DROP TABLE TAB_P;
Table dropped.
orcl>CREATE TABLE TAB_P(ID NUMBER(3));
Table created.
orcl>INSERT INTO TAB_P(ID) VALUES(123);
1 row created.
orcl>INSERT INTO TAB_P(ID) VALUES(1234);
INSERT INTO TAB_P(ID) VALUES(1234)
                             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

Hope this clears your doubts!!

I think the above mentioned range is not clear.The values are below

1 x 10-130 to 9.99...9 x 10125

Okay. So the NUMBER can accept a maximum value of 38 nines followed by 88 zeros.

Thank you all.

Ora-aff wrote:

Okay. So the NUMBER can accept a maximum value of 38 nines followed by 88 zeros. 

Thank you all.

The precision is the MAXIMUM number of SIGNIFICANT digits. Numbers larger than 38 digits can be represented as long as there are no more than 38 significant digits.

 The precision is the MAXIMUM number of SIGNIFICANT digits. Numbers larger than 38 digits can be represented as long as there are no more than 38 significant digits.

And the definition of significant digit in Oracle is different than what Mathematics has, correct?

In Mathematics, a number 5600 has only two significant digits 5 and 6. But in Oracle it has four significant digits including the two 0. Am I right?

No.

In mathematics (source):

1. If the number has a decimal point, the leading "0"s are not significant. All other digits (including trailing "0"s) are significant.

2. If the number is a whole number, the trailing "0"s are not significant. All other digits (including "0"s) are significant. If some of the trailing "0"s are to be considered significant, we should write them in scientific notation to show this.

So a number 1000 has only one significant digit as per above definitions. But a column of NUMBER(3) will not accept it throwing:

"SQL Error: ORA-01438: value larger than specified precision allowed for this column".

Is this because Oracle is treating 1000 as 1000. (<--- note the decimal after 1000)???? Then only I see 1000. has 4 significant digits as per above definitions.

That is because you constraining with number(3), with number you do not want any constraint, so you are limited by Oracle's range as mentioned above. When you say number(3) you are saying you do not want anymore than three digits in that column, with number you are saying let anything in that Oracle can handle:

sql> create table bc_test (col1 number);

Table created.

sql> insert into bc_test values (1e38);

1 row created.

sql> insert into bc_test values (1e88);

1 row created.

sql> insert into bc_test values (1e125);

1 row created.

sql> insert into bc_test values (1e126);
insert into bc_test values (1e126)
                            *
ERROR at line 1:
ORA-01426: numeric overflow

sql> drop table bc_test;

Table dropped.

sql> create table bc_test (col1 number(3));

Table created.

sql> insert into bc_test values (1e1);

1 row created.

sql> insert into bc_test values (1e2);

1 row created.

sql> insert into bc_test values (1e3);
insert into bc_test values (1e3)
                            *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

Two different scenarios, two different error messages.

Ora-aff wrote:

In mathematics (source):

1. If the number has a decimal point, the leading "0"s are not significant. All other digits (including trailing "0"s) are significant.
2. If the number is a whole number, the trailing "0"s are not significant. All other digits (including "0"s) are significant. If some of the trailing "0"s are to be considered significant, we should write them in scientific notation to show this.

So a number 1000 has only one significant digit as per above definitions. But a column of NUMBER(3) will not accept it throwing:
"SQL Error: ORA-01438: value larger than specified precision allowed for this column".

Is this because Oracle is treating 1000 as 1000. (<--- note the decimal after 1000)???? Then only I see 1000. has 4 significant digits as per above definitions. 

Firstly, it is not quite as unambiguous as your source says (Significant figures - Wikipedia, the free encyclopedia):

• The significance of trailing zeros in a number not containing a decimal point can be ambiguous. For example, it may not always be clear if a number like 1300 is precise to the nearest unit (and just happens coincidentally to be an exact multiple of a hundred) or if it is only shown to the nearest hundred due to rounding or uncertainty. Various conventions exist to address this issue:

• A bar may be placed over the last significant figure; any trailing zeros following this are insignificant. For example, 1300 has three significant figures (and hence indicates that the number is precise to the nearest ten).

• The last significant figure of a number may be underlined; for example, "2000" has two significant figures.

• A decimal point may be placed after the number; for example "100." indicates specifically that three significant figures are meant.^[2]

• In the combination of a number and a unit of measurement, the ambiguity can be avoided by choosing a suitable unit prefix. For example, the number of significant figures in a mass specified as 1300 g is ambiguous, while in a mass of 13 hg or 1.3 kg it is not.

Secondly, Oracle assumes you want 1000 to be treated as having 4 significant figures. If you want otherwise, use a negative scale.

drop table test_prec;

create table test_prec (

num_field number(1,-3)

);

insert into test_prec

values(9111);

select *

from test_prec;

9000

And the definition of significant digit in Oracle is different than what Mathematics has, correct?
In Mathematics, a number 5600 has only two significant digits 5 and 6. But in Oracle it has four significant digits including the two 0. Am I right?

Yes and no.

The definition Oracle uses is in the doc that Kark gave you the link to:

p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit.

This is the definition in that 'source' you linked:

If the number has a decimal point, the leading "0"s are not significant. All other digits (including trailing "0"s) are significant.
If the number is a whole number, the trailing "0"s are not significant. All other digits (including "0"s) are significant. If some of the trailing "0"s are to be considered significant, we should write them in scientific notation to show this. 

Oracle actually stores NUMBER values in a scientific notation format but, as Paul illustrated, you need to tell Oracle what precision and scale to use to convert the 'literal' you give it.

Hence the confusion. All of these values will take the SAME amount of space:

create table test_numbers (col1 number)
insert into test_numbers values (.0000000000000001);
insert into test_numbers values (.0000000000001);
insert into test_numbers values (.0000000001);
insert into test_numbers values (.0000001);
insert into test_numbers values (.0001);
insert into test_numbers values (.1);
insert into test_numbers values (1);
insert into test_numbers values (10);
insert into test_numbers values (100);
insert into test_numbers values (1000);
insert into test_numbers values (1000000);
insert into test_numbers values (1000000000);
insert into test_numbers values (1000000000000);
insert into test_numbers values (1000000000000000);
select col1, dump(col1) from test_numbers;

COL1,DUMP(COL1)
1E-16,Typ=2 Len=2: 185,2
1E-16,Typ=2 Len=2: 185,2
1E-13,Typ=2 Len=2: 186,11
1E-10,Typ=2 Len=2: 188,2
1E-7,Typ=2 Len=2: 189,11
0.0001,Typ=2 Len=2: 191,2
0.1,Typ=2 Len=2: 192,11
1,Typ=2 Len=2: 193,2
10,Typ=2 Len=2: 193,11
100,Typ=2 Len=2: 194,2
1000,Typ=2 Len=2: 194,11
1000000,Typ=2 Len=2: 196,2
1000000000,Typ=2 Len=2: 197,11
1000000000000,Typ=2 Len=2: 199,2
1E15,Typ=2 Len=2: 200,11

The length is '2' for ALL of those values. For storage purposes Oracle treated trailing (leading for fractional numbers) zeroes as NOT significant.

But if you try to store those values in a NUMBER column defined with a specified precision the trailing zeroes are SIGNIFICANT it telling Oracle what the KNOWN value of the literal is.