Exasol7 - Virtual schema issue

[gcekol]

Hi!

I have created a view on Oracle database (date, varchar2 and number data types).

On Exasol I have created virtual schema and I see this view as a table. The problem is that all the fields except that data files have varchar data type.

How can this be solved?

I am afraid that I won't be able to summarize numeric fields.

Thank you in advance!

Goran

[gcekol]

It seems that is normal behaviour.

Mapping of Number Types:
NUMBER, NUMBER with precision > 36 and LONG are casted to VARCHAR to prevent a loss of precision.

If you want to return a DECIMAL type for these types you can set the property ORACLE_CAST_NUMBER_TO_DECIMAL_WITH_PRECISION_AND_SCALE: This will cast NUMBER with precision > 36, NUMBER without precision and LONG to DECIMAL(36,20). Keep in mind that this will yield errors if the data in the Oracle database does not fit into the specified DECIMAL type.

Do you know where could I test this settings, how to implement it?

Thank you!

Goran

[mwellbro · Accepted solution]

Hi Goran,

to activate this property a simple 

ALTER VIRTUAL SCHEMA <your-schema-name-goes-here> SET ORACLE_CAST_NUMBER_TO_DECIMAL_WITH_PRECISION_AND_SCALE='36,20';

will suffice, you´ll have to refresh the virtual schema afterwards with

alter virtual schema <your-schema-name-goes-here> refresh;

( tested on my mentioned system, but I´d expect it to work on your setup as well ).

[gcekol]

Thank you very much, it worked! 🙂

[gcekol]

It seems if on Oracle data type is NUMBER then in Exasol it gets VARCHAR datatype, but if it's NUMBER(2) then in Exasol it gets DECIMAL data type.

[mwellbro]

if it´s pure NUMBER without precision and scale that might even be a sensible way since the decimal in EXA does not cover as much values as in ORA...

[gcekol]

I have tried with virtual-schema-dist-7.0.0-bundle-4.0.4,jar and still no change.

[mwellbro]

Hi @gcekol ,

just to clarify: your on DB version 7.0 ( .x ? ) and using virtual-schema-dist-7.0.0-bundle-4.0.4.jar ?

It´s the first time I hear something like that, can´t verify it on DB 6.2.8 using virtual-schema-dist-5.0.4-bundle-4.0.3.jar , there the data type mapping seems to work correctly.

Cheers,
Malte

[gcekol]

Exasol version is 7.0.3 (Community).

I have used "virtual-schema-dist-5.0.4-bundle-4.0.3,jar"

I'm using DBVisualizer.

Thx!

Goran