0

I have been assuming based on old posts I found here and elsewhere that a NUMBER data type with no scale specified defaults to zero. However, when I look at our data on our server, I see rational numbers (read non-integers) even with no scale specified. Is this expected behavior?

To give some context, I am a technical writer and I am documenting various things associated with the data we house. I have been assuming that all NUMBERS with no scale specified have been integers. If this is incorrect, I'll need to update my documentation.

I have basically used the following link as a citation for my reasoning concerning scale, but now I believe that it is outdated. Link

I have also read the oracle documentation here and here. The last link also states that if no scale is specified, then the default is set to zero.

Matthew Yocum
  • 19
  • 5
  • 2
    You mean [the current documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-75209AF6-476D-4C44-A5DC-5FA70D701B78)? – Alex Poole Jun 11 '19 at 20:24
  • I guess I lack the know-how for how to determine if its the current documentation or not. So, to make sure I am reading this correctly, all NUMBERs with no p/s specified are float-point numbers? – Matthew Yocum Jun 11 '19 at 20:30
  • Yes, essentially, as Gordon said. – Alex Poole Jun 11 '19 at 20:36
  • 2
    "Up to date" documentation in this case is not really meaningful. Precision and scale have worked the same way going back at least a very large number of versions; and in each version, the documentation uses the same confusing language (which is then explained very well in Gordon's answer). –  Jun 11 '19 at 20:50

1 Answers1

1

I think the Oracle documentation is misleading. If no precision and no scale is specified, then the number is stored as-is; the default to 0 scale only occurs when there is a specified precision.

So, the following code stores the first few digits of pi:

create table t (n number);

insert into t(n) values (3.14159265358979);

But this does not:

create table t2 (n number(5));

insert into t2(n) values (3.14159265358979);

Here is a db<>fiddle illustrating this.

This is rather indelibly marked in my memory, from a fun time porting an Oracle database to BigQuery (which did not even have numeric at the time). The Oracle number data type was one of the most difficult parts of the transition. In the end, we needed to move that into strings.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Okay, that's interesting. So then all my NUMBER data types with no p/s specified will be rationals, but might only be integers by convention. – Matthew Yocum Jun 11 '19 at 20:26
  • @MatthewYocum . . . Yes. In my case, I discovered that these numbers were sometimes used for codes, that the codes on occasion had decimal points, and zeros after the decimal point were significant. – Gordon Linoff Jun 11 '19 at 20:27