Difference between number and integer datatype in oracle dictionary views

Question

I used oracle dictionary views to find out column differences if any between two schema s  While syncing data type discrepancies I found that both NUMBER and INTEGER data types stored in all tab columns user tab columns dba tab columns as NUMBER only so it is difficult to sync data type discrepancies where one schema column has number datatype and another schema column has integer data type   While comparison of schema s it show datatype mismatch  Please suggest if there is any other alternative apart form using dictionary views or if any specific properties from dictionary views can be used to identify if data type is integer

User · Answer

This is what I got from oracle documentation, but it is for oracle 10g release 2:

When you define a NUMBER variable, you can specify its precision (p) and scale (s) so that it is sufficiently, but not unnecessarily, large. Precision is the number of significant digits. Scale can be positive or negative. Positive scale identifies the number of digits to the right of the decimal point; negative scale identifies the number of digits to the left of the decimal point that can be rounded up or down.

The NUMBER data type is supported by Oracle Database standard libraries and operates the same way as it does in SQL. It is used for dimensions and surrogates when a text or INTEGER data type is not appropriate. It is typically assigned to variables that are not used for calculations (like forecasts and aggregations), and it is used for variables that must match the rounding behavior of the database or require a high degree of precision. When deciding whether to assign the NUMBER data type to a variable, keep the following facts in mind in order to maximize performance:

Analytic workspace calculations on NUMBER variables is slower than other numerical data types because NUMBER values are calculated in software (for accuracy) rather than in hardware (for speed).
When data is fetched from an analytic workspace to a relational column that has the NUMBER data type, performance is best when the data already has the NUMBER data type in the analytic workspace because a conversion step is not required.

User · Answer

the best explanation i ve found is this    What is the difference betwen INTEGER and NUMBER  When should we use NUMBER and when should we use INTEGER  I just wanted to update my comments here     NUMBER always stores as we entered  Scale is -84 to 127  But INTEGER rounds to whole number  The scale for INTEGER is 0  INTEGER is equivalent to NUMBER 38 0   It means  INTEGER is constrained number  The decimal place will be rounded  But NUMBER is not constrained    INTEGER 12 2     12  INTEGER 12 5     13  INTEGER 12 9     13 INTEGER 12 4     12  NUMBER 12 2     12 2  NUMBER 12 5     12 5 NUMBER 12 9     12 9  NUMBER 12 4     12 4   INTEGER is always slower then NUMBER  Since integer is a number with added constraint  It takes additional CPU cycles to enforce the constraint  I never watched any difference  but there might be a difference when we load several millions of records on the INTEGER column  If we need to ensure that the input is whole numbers  then INTEGER is best option to go  Otherwise  we can stick with NUMBER data type   Here is the link

User · Answer

Integer is only there for the sql standard ie deprecated by Oracle     You should use Number instead   Integers get stored as Number anyway by Oracle behind the scenes   Most commonly when ints are stored for IDs and such they are defined with no params - so in theory you could look at the scale and precision columns of the metadata views to see of no decimal values can be stored - however 99  of the time this will not help   As was commented above you could look for number 38 0  columns or similar  ie columns with no decimal points allowed  but this will only tell you which columns cannot take decimals  and not what columns were defined so that INTS can be stored   Suggestion  do a data profile on the number columns  Something like this    select max  case when trunc column name 0  column name then 0 else 1 end   as has dec vals  from table name

[oracle11g] Difference between number and integer datatype in oracle dictionary views

Examples related to oracle11g