19-11-2020 04:20 PM
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
Solved! Go to Solution.
19-11-2020 09:49 PM
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 ).
19-11-2020 07:03 PM
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
19-11-2020 09:49 PM
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 ).
19-11-2020 10:10 PM
Thank you very much, it worked! 🙂
19-11-2020 06:52 PM
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.
19-11-2020 07:04 PM
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...
19-11-2020 06:46 PM - edited 19-11-2020 06:46 PM
I have tried with virtual-schema-dist-7.0.0-bundle-4.0.4,jar and still no change.
19-11-2020 04:31 PM
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
19-11-2020 06:26 PM
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
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In