21

recently, while working on a db2 -> oracle migration project, we came across this situation. the developers were inadvertently creating new table structures using decimal(s,p) columns. I didn't remember Oracle supporting this, but then some digging showed that its a ANSI data type therefore supported by oracle.

However, question for me remained -

  1. how is this data handled internally ?
  2. is there a cost of using ANSI types instead of Oracle's built in types ?
  3. Will there be an impact during the data migration if the target type was Oracle built-in type ?
Thomas Dickey
  • 51,086
  • 7
  • 70
  • 105
Raghav
  • 2,128
  • 5
  • 27
  • 46

2 Answers2

21

In Oracle, they are the same:

SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type based on the conversions shown in the tables that follow.

The table below this quote shows that DECIMAL(p,s) is treated internally as a NUMBER(p,s):

SQL> create table t (a decimal(*,5), b number (*, 5));

Table created

SQL> desc t;
Name Type        Nullable Default Comments 
---- ----------- -------- ------- -------- 
A    NUMBER(*,5) Y                         
B    NUMBER(*,5) Y

However, the scale defaults to 0 for DECIMAL, which means that DECIMAL(*) is treated as NUMBER(*, 0), i.e. INTEGER:

SQL> create table t (a decimal, b number, c decimal (5), d decimal (5));

Table created

SQL> desc t;
Name Type      Nullable Default Comments 
---- --------- -------- ------- -------- 
A    INTEGER   Y                         
B    NUMBER    Y                         
C    NUMBER(5) Y                         
D    NUMBER(5) Y
real_paul
  • 574
  • 2
  • 22
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • 1
    There is no data conversion: `DECIMAL` is stored and treated as a `NUMBER`. – Vincent Malgrat Jun 14 '12 at 11:04
  • 3
    @learn4living - Oracle is not converting the data (values stored in column) it is converting the metatdata (what is stored in the data dictionary). So the only cost is the price you are paying now: confusion amongst Oracle practitioners faced with non-Oracle datatypes. – APC Jun 14 '12 at 11:06
  • Well, in our project, the scale defaulting to zero made the diff and the research worthwhile. Thanks everyone for the pointers. – Raghav Jun 16 '12 at 08:32
  • 4
    @VincentMalgrat: DECIMAL is not *exactly* the same as NUMBER. As defined in SYS.STANDARD DECIMAL is defined as NUMBER(38, 0), so a variable defined as DECIMAL cannot contain any digits to the right of the decimal point, while something defined as NUMBER can. To have DECIMAL values with digits to the right of the decimal point they have to be defined as e.g. DECIMAL(38, 4). Share and enjoy. – Bob Jarvis - Слава Україні Jul 17 '13 at 13:21
-3

Actually, there is difference between decimal and number. Decimal will truncate the value which is over-scale, number will round the value.

JasonMing
  • 561
  • 8
  • 16