NUMBER data type 1

[RiverGuy]

I'm creating staging tables in SQL Server to ETL data over from Oracle 11g. Using Oracle SQL Developer, I am looking at the DDL for many of these tables. Many of the columns are defined as such:

Some_Column_Name NUMBER

Since many of these columns are currently filled with NULLs, I'm not sure what the data looks like. That being said, if the data type NUMBER is specified without a precision and scale, what exactly does that mean? Is it an integer, or a dynamically scaled decimal data type?

 

[SantaMufasa]

Is it an integer, or a dynamically scaled decimal data type?

It is the latter.


In Oracle, under normal circumstances (i.e., explicitly defined precision), your maximum precision is 38 digits:

create table test (x number(39));
create table test (x number(39))
                            *
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)

Strangely, however, the maximum size of default (i.e., implicitly defined) precision is much larger:

SQL> create table test (x number);

Table created.

SQL> desc test
 Name                    Null?    Type
 ----------------------- -------- ------
 X                                NUMBER

insert into test values(power(10,125));

1 row created.

insert into test values(power(10,126))
                        *
ERROR at line 1:
ORA-01426: numeric overflow

So, you can see that the maximum store value for an unbounded Oracle NUMBER data declaration is somewhere between 10¹²⁵ and 10¹²⁵. Regardless of the maximum value stored in an Oracle NUMBER column, Oracle accuracy remains at 40 digits of precision.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[RiverGuy]

Bummer. Looks like I have some SQL Server tables to drop and recreate. Thanks for the explanation Dave.

 

[SantaMufasa]

Ooops...I hope you caught that what I really meant above was "between 10¹²⁵ and 10¹²⁶"

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[RiverGuy]

I inferred that from your two insert statements.