I'm working on oracle database and I need to find the range (minimum,maximum) for a format NUMBER(P,S). I know this for a positive scale but not for negative scale.

Example for positive scale -

NUMBER(6,2) : {maximum:9999.99,minimum:-9999.99}

I'm not sure but I think for negative scale it should be like this-

NUMBER(7,-2) : {maximum:999999900,minimum:-999999900} // not sure

Is this correct? I can't find any explanations regarding negative scale, just couple of lines here and there. Can anyone help?

CodePudding user response：

In a NUMBER(6, -2) there are 6 digits of precision and a scale of -2 means there are -2 digits to the right of the decimal point (or, equivalently, 2 zero-digits to the left of the decimal point) so the maximum number is 99999900 and the minimum is -99999900 (and numbers are stored rounded to the nearest 100).

The documentation states:

Specify a fixed-point number using the following form:

NUMBER(p,s)

where:

• p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.

• s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.

  • Positive scale is the number of significant digits to the right of the decimal point to and including the least significant digit.
  • Negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For negative scale the least significant digit is on the left side of the decimal point, because the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.