CODEWITHSUNDEEP
oracle-database
Juan Carlos Villalobos
Juan Carlos Villalobos

Reputation: 55

Why oracle doesn't show the integer part of a decimal

when I execute the next SQL:

select 1/3 from dual;

the result is ,333333333 but the result that I expected was 0,333333333

Why doesn't oracle show the zero ? is there any parameter that can affect it? Thanks

Edit: I checked this behaviour from an aplication that uses the oo4o (oracle object for ole), and confirmed with the sqlDeveloper (when I execute the sql with F5, not with ctrl+intro). The real problem is with the oo4o Dynaset object: when I try to sum the value of two fields, what I get is a concatenation, not a sum: ,3 + ,2 = ,3,2. What I want to know is if there is some parameter or configuration that makes the oracle engine return the number without the zero, so I can turn on|off in order to return de zero as integer part.

Upvotes: 0

Views: 1294

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

SQL*Plus will show that by default (using your territory's decimal separator):

SQL> select 1/3 from dual;

       1/3
----------
.333333333

You could use set numformat to change the behaviour:

SQL> set numformat "0D9999"
SQL> select 1/3 from dual;

    1/3
-------
 0.3333

... where the D represents the decimal separator. Or you could use column formatting, with a column alias:

SQL> set numformat ""
SQL> column answer format 0.000
SQL> select 1/3 as answer from dual;

ANSWER
------
 0.333

Other clients have different ways of controlling the default output; SQL Developer behaves much the same, but PL/SQL Developer, Toad etc. might not.

Or you can format the number as part of the query, which isn't client-dpendent:

SQL> select to_char(1/3, '9990D99999') from dual;

TO_CHAR(1/3
-----------
    0.33333

You need to provide enough digits for the integer part of whatever you're calculating though. Anything less than zero is simple, but if there are too many digits before the decimal separator then it won't display at all:

SQL> select to_char(100000/3, '9990D99999') from dual;

TO_CHAR(100
-----------
###########

Upvotes: 3

Related Questions