This discussion is archived
12 Replies Latest reply: Mar 30, 2013 3:11 PM by rp0428 RSS

Number datatypes

Gusora Newbie
Currently Being Moderated
Okay rather simple question here. When I look at the number datatype declaration for a database table column such as this....

NUMBER 4.56 will be stored as 4.56
NUMBER(2) 4.56 ditto 5
NUMBER(5,2) 4.56 ditto 4.56

NUMBER(p,s), it clearly says in the documentation that if neither is specified p defaults to the max value and s defaults to zero. Then why when I use the first declaration, 4.56 is stored. Surely if the scale is not specified it is zero and the .56 should be knocked off and 5 should be stored?
  • 1. Re: Number datatypes
    SomeoneElse Guru
    Currently Being Moderated
    NUMBER(p,s), it clearly says in the documentation that if neither is specified p defaults to the max value and s defaults to zero.
    Please post the link.

    Here's what I saw:

    "The absence of precision and scale designators specifies the maximum range and precision for an Oracle number."
  • 2. Re: Number datatypes
    rp0428 Guru
    Currently Being Moderated
    >
    Okay rather simple question here. When I look at the number datatype declaration for a database table column such as this....

    NUMBER 4.56 will be stored as 4.56
    NUMBER(2) 4.56 ditto 5
    NUMBER(5,2) 4.56 ditto 4.56

    NUMBER(p,s), it clearly says in the documentation that if neither is specified p defaults to the max value and s defaults to zero. Then why when I use the first declaration, 4.56 is stored. Surely if the scale is not specified it is zero and the .56 should be knocked off and 5 should be stored?
    >
    See the NUMBER Datatype in the SQL Language Doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#i54330
    >
    Specify an integer using the following form:

    NUMBER(p)
    This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

    Specify a floating-point number using the following form:

    NUMBER
    The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.
  • 3. Re: Number datatypes
    Gusora Newbie
    Currently Being Moderated
    Please see this link [Overview of Numeric Datatypes, Oracle® Database Concepts 11g Release 1|http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i16209]

    Look for table 26-1 further down the page. And just above it, it is stated.... If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.

    Sorry if the link text above is not appearing properly as a clickable link but when I tried to insert the link using the link tool two text boxes dropped down:

    In the URL box on the left I put .... http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i16209
    And in the Link text box on the right I put ... Overview of Numeric Datatypes, Oracle® Database Concepts 11g Release 1

    So not sure what I've done wrong there!?!

    Edited by: Gusora on Mar 30, 2013 8:18 PM

    Edited by: Gusora on Mar 30, 2013 8:32 PM
  • 4. Re: Number datatypes
    Gusora Newbie
    Currently Being Moderated
    NUMBER
    The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.
    Just wondering why it doesn't say ....

    The absence of precision and scale designators specifies the maximum scale and precision for an Oracle number. See my response above with a link included.
  • 5. Re: Number datatypes
    Etbin Guru
    Currently Being Moderated
    [url http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i16209]11g Release 1 says (each comment applies to the colored line above it):

    For numeric columns, you can specify the column as:
    <font color="red"><b>column_name NUMBER</b></font>
    Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the decimal point):
    <font color="blue"><b>column_name NUMBER (precision, scale)</b></font>
    If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.
    Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:
    <font color="green"><b>column_name NUMBER (*, scale)</b></font><br>
    In this case, the precision is 38, and the specified scale is maintained.<br><br>
    [url http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#CBBFFHEB]11g Release 2 might be more clear:

    You specify a fixed-point number in the form NUMBER(p,s), where p and s refer to the following characteristics:
    Precision
    The precision specifies the total number of digits. If a precision is not specified, then the column stores the values exactly as provided by the application without any rounding.
    Scale
    The scale specifies the number of digits from the decimal point to the least significant digit. Positive scale counts digits to the right of the decimal point up to and including the least significant digit. Negative scale counts digits to the left of the decimal point up to but not including the least significant digit. If you specify a precision without a scale, as in NUMBER(6), then the scale is 0.

    Regards

    Etbin
  • 6. Re: Number datatypes
    Gusora Newbie
    Currently Being Moderated
    Sorry Etbin I still don't get it.

    You said ...
    If you specify a precision without a scale, as in NUMBER(6), then the scale is 0.

    Okay so If I pass 5.64 to your datatype above what will be stored?
  • 7. Re: Number datatypes
    Peter Gjelstrup Guru
    Currently Being Moderated
    Okay so If I pass 5.64 to your datatype above what will be stored?
    SQL> create table t (x number(6));
    
    Table created.
    
    SQL> insert into t values(5.64);
    
    1 row created.
    
    SQL> select dump(x) from t;
    
    DUMP(X)
    -----------------------------------
    Typ=2 Len=2: 193,7
    
    SQL> select x from t;
    
             X
    ----------
             6
    
    SQL>
    +" Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it."+
  • 8. Re: Number datatypes
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    look table 26-1
    http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i22289


    table 26-1
    http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#g23242

    Edited by: Ramin Hashimzadeh on Mar 31, 2013 1:26 AM
  • 9. Re: Number datatypes
    Gusora Newbie
    Currently Being Moderated
    Okay now I get it...
    create table deletethis(
    first number,
    second number(2),
    third number(5, 2)
    );
    
    insert into deletethis values(4.56, 4.56, 4.56);
    
    select * from deletethis;
    
    4.56       5     4.56 
    The scale and precision is maintained only if a range for both is not specified. In the guidance it says this

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

    Which I think is a bit misleading, as shown for column one output above.

    I am aware this is balanced out as Oracle maintains the (p,s) when neither is specified. Things are always so obvious when its pointed out :)
  • 10. Re: Number datatypes
    rp0428 Guru
    Currently Being Moderated
    >
    Just wondering why it doesn't say ....

    The absence of precision and scale designators specifies the maximum scale and precision for an Oracle number.
    >
    Because the phrase 'maximum scale and precision' is NOT correct!

    The specification 'NUMBER' doesn't imply 'maximum scale', 'minimum scale' or any scale at all. That specification does ALLOW you to store a value with the maximum scale and long as the 'maximum precision' is not exceeded. That spec also ALLOWs you to store a value with the minimum scale or no scale at all.

    NUMBER allows you to specify 38 significant digits all of which or NONE of which represent scale.

    The doc statement I quoted is the correct one
    >
    The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.
    >
    The spec NUMBER allows for the 'maximum range AND precision'. It ALLOWs for but doesn't specify the MAX, MIN or any other specific value.
  • 11. Re: Number datatypes
    Gusora Newbie
    Currently Being Moderated
    Okay I obviously didn't know what I was talking about there...


    p = can range from 1 to 38
    s = can range from -84 to 127

    I think I'm going to have to relearn my school maths, counting significant digits in numbers with and without decimal points.

    God help me when I get onto timestamp data types with and without timezones!!!
  • 12. Re: Number datatypes
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    God help me when I get onto timestamp data types with and without timezones!!!
    :-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points