DECIMAL and NUMBER with precision

[BlooKe]

Hi there!

Is there a way to handle DECIMAL and NUMBER data types with digits after decimal point? As at the moment go-oci8 uses int64 for SQLT_NUM.

And is support for DECIMAL/NUMBER planned in nearest future?

[MichaelS11]

In sql plus couldn't get any digits after decimal point in 11g. Where you getting different results?

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 08:45:12 2018

...

08:45:12 SQL> select cast (-123.456 as DECIMAL) from dual;

CAST(-123.456ASDECIMAL)
-----------------------
                   -123

1 row selected.

Elapsed: 00:00:00.01
08:45:13 SQL> select cast (-123.456 as NUMERIC) from dual;

CAST(-123.456ASNUMERIC)
-----------------------
                   -123

1 row selected.

Elapsed: 00:00:00.01

Casting as float or others works just fine:

08:47:48 SQL> select cast (-123.456 as FLOAT) from dual;

CAST(-123.456ASFLOAT)
---------------------
             -123.456

1 row selected.

Elapsed: 00:00:00.01

[BlooKe]

Hi @MichaelS11 ! You are casting without precision and scale! By default, if I remember right, precision ir 38, and scale is 0, which means, that there will not be any digits after decimal point.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select cast (-123.456 as DECIMAL) from dual;
CAST(-123.456ASDECIMAL)
-----------------------
                   -123

SQL> select cast (-123.456 as DECIMAL(12,5)) from dual;
CAST(-123.456ASDECIMAL(12,5))
-----------------------------
                     -123.456

SQL> select cast (-123.456 as NUMERIC) from dual;
CAST(-123.456ASNUMERIC)
-----------------------
                   -123

SQL> select cast (-123.456 as NUMERIC(12,5)) from dual;
CAST(-123.456ASNUMERIC(12,5))
-----------------------------
                     -123.456

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select cast (-123.456 as DECIMAL) from dual;
CAST(-123.456ASDECIMAL)
-----------------------
                   -123

SQL> select cast (-123.456 as DECIMAL(12,5)) from dual;
CAST(-123.456ASDECIMAL(12,5))
-----------------------------
                     -123.456

SQL> select cast (-123.456 as NUMERIC) from dual;
CAST(-123.456ASNUMERIC)
-----------------------
                   -123

SQL> select cast (-123.456 as NUMERIC(12,5)) from dual;
CAST(-123.456ASNUMERIC(12,5))
-----------------------------
                     -123.456

SQL> select cast (-123.456 as NUMBER(12,5)) from dual;
CAST(-123.456ASNUMBER(12,5))
----------------------------
                    -123.456

[MichaelS11]

Thank you.

This should now be fixed. please test.

[BlooKe]

Works OK! Thanks @MichaelS11

[MichaelS11]

Welcome :)