0

I have a field that stores version information such as 1.1, 1.2 etc. However, it will store 4.0 as 4 and 5.0 as 5.

Is there a way to force it to show the .0?

I have tried NUMBER(5, 3) and DECIMAL(5, 3) data types and neither work.

Version - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

hacket
  • 1,171
  • 4
  • 13
  • 25

2 Answers2

7

A NUMBER column will never store the decimal value if it doesn't need to.

You can, however, control how the data is displayed to the user when it is converted to a string by using an explicit TO_CHAR rather than relying on implicit data type conversion. If you use the format mask 9.0 in your TO_CHAR call, for example, the string that is generated will always have 1 decimal digit.

SQL> select to_char( 4, '9.0' )
  2    from dual;

TO_C
----
 4.0

SQL> ed
Wrote file afiedt.buf

  1  select to_char( 4.1, '9.0' )
  2*   from dual
SQL> /

TO_C
----
 4.1

That being said, it seems unlikely that you would really want to store software version information in a NUMBER column rather than a VARCHAR2. You, presumably, aren't doing any mathematical operations on the data so most of the benefit of having a NUMBER is off the table. And software versions are not inherently numeric. You often find yourself wanting more than two components (i.e. Oracle 11.2.0.1). And software versions tend to have highly non-numeric things done to them. If the version that follows 4.9 is 4.10, that's very different than the version 4.1 that followed 4.0. If you're using a NUMBER, though, you lose the ability to differentiate between those versions.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I feared as much. Thank you for the solution. – hacket May 08 '13 at 19:28
  • 1
    @hacket - also note that `DECIMAL` and `NUMBER` are the same thing. There's a StackOverflow post about that [here](http://stackoverflow.com/questions/11031409/decimals-p-or-numbers-p), and it includes a reference to the Oracle docs. If you specify a column as `DECIMAL`, Oracle will create it as a `NUMBER`. – Ed Gibbs May 08 '13 at 19:56
0

If you need to hide this from your code, create a view to hide these details.

It will however, be a string for the same reason Justin indicates.

marceljg
  • 997
  • 4
  • 14