Connecting to an Oracle database with OLEDB results in lots of trailing decimal zeroes.

Connecting to an Oracle database with OLEDB results in lots of trailing decimal zeroes.

This happens if the data type is defined as "number" in Oracle, without setting the precision and scale attributes.

Example:

CREATE TABLE "TIQ"."TEST_NUMBER"
  ("COLUMN1" NUMBER,
   "COLUMN2" NUMBER(38,0)  );
Insert into TIQ.TEST_NUMBER (COLUMN1,COLUMN2) values (1,2);
Insert into TIQ.TEST_NUMBER (COLUMN1,COLUMN2) values (3,4);

If you load this into QlikView via OLE DB x64 the COLUMN1 will have the issue, but COLUMN2 will not.

You can fix this either in the database by changing the column type, or in the query by using CAST(COLUMN1 as NUMBER(38)) or by using num#( ) function in QlikView.

If no precision and no scale are specified for an Oracle NUMBER column then it can contain arbitrary floating point numbers, and floating point numbers can be large.