2

I created a column in a table in my oracle DB of type NUMBER but in creating this column, I left the precision and scale blank.

When I was using the java.sql's ResultSetMetadata class to obtain the column information, I got back that the column precision was 0 and that the scale was -127.

The Oracle docs say that

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

But what does that mean in this case? How is the precision being returned equal to 0 and the scale a negative number?

Should I be obtaining the scale and precision in a different manner?

Karan
  • 1,335
  • 2
  • 14
  • 29
  • 1
    Related (possibly duplicate): https://stackoverflow.com/questions/1410267/oracle-resultsetmetadata-getprecision-getscale?rq=1 and https://stackoverflow.com/questions/593197/what-is-the-default-precision-and-scale-for-a-number-in-oracle?rq=1 – Mark Rotteveel Sep 06 '17 at 06:52
  • Thanks! this does shed some light on the issue but i am still confused as to why the scale being returned is -127. – Karan Sep 06 '17 at 06:55
  • 1
    As far as I can tell, Oracle uses -127 as a signal that it is not a normal scale, but has special meaning. – Mark Rotteveel Sep 06 '17 at 06:56
  • I did some digging around this question with the [Postgres driver](https://stackoverflow.com/questions/45855174/how-to-handle-the-loss-of-precision-on-jdbc-numeric-types-due-to-grouping-functi), which returns `0,0` when not explicitly specified. This allows the server to store it as it likes (and with enough bytes), but it'll also result in metadata giving values that need to be specially handled. – Kayaman Sep 06 '17 at 06:59
  • 1
    In my experience -127 is stored in `all_tab_columns.data_precision` (which is what the driver returns for the `DECIMAL_DIGITS`) when a column is created as `number` (without specifying any size or precision). –  Sep 06 '17 at 07:04

1 Answers1

3

Oracle returns -127 when the scale is unspecified. This is mentioned in the: JavaDoc.

Jean de Lavarene
  • 3,461
  • 1
  • 20
  • 28