Exasol7 - Virtual schema issue

Contributor

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

1 ACCEPTED SOLUTION

Xpert

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 ).

 

 

View solution in original post

8 REPLIES 8

Contributor

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

Xpert

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 ).

 

 

View solution in original post

Contributor

Thank you very much, it worked! 🙂

Contributor

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.

Xpert

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...

Contributor

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

Xpert

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

Contributor

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