In TIBCO Data Virtualization for an Oracle OCI driver, the datatype NUMBER(38,0) gets introspected as NUMBER(0,0) because of which the datatype gets mapped to TINYINT and not to DECIMAL(38,0)

This article explains how to map the datatype NUMBER(38,0) to DECIMAL(38,0) for an Oracle OCI driver that maps it to TINYINT.

All supported environment

One must follow the below steps to map the datatype correctly. In the below example Oracle 11g OCI driver has been taken into consideration, however the same can be added to other versions as well, for e.g. Oracle 19c OCI driver.

1) First take a backup of current file “oracle_11g_oci_driver_values.xml.” which is located under the path <TDV Install Dir> \conf\adapters\system\oracle_11g_oci_driver
2) Make the following change in the oracle_11g_oci_driver_values.xml file.

Add the following snippet to the oracle_11g_oci_driver_values.xml file :
------------------------------------------------------------------------------------------------------------------------------------------------
    <common:attribute xmlns:common="http://www.compositesw.com/services/system/util/common">
    <common:name>/introspection/datatype/numeric/overrideUnspecifiedPrecision</common:name>
    <common:type>INTEGER</common:type>
    <common:value>38</common:value>
    <common:configID>introspect.datatype.numeric.override_unspecified_precision</common:configID>
    </common:attribute>

    <ns1:attribute xmlns:ns1="http://www.compositesw.com/services/system/util/common">
    <ns1:name>/introspection/datatype/numeric/overrideUnspecifiedScale</ns1:name>
    <ns1:type>INTEGER</ns1:type>
    <ns1:value>0</ns1:value>
    <ns1:configID>introspect.datatype.numeric.override_unspecified_scale</ns1:configID>
    </ns1:attribute>
------------------------------------------------------------------------------------------------------------------------------------------------

3) After making the change restart the TDV Server.
4) Then from the studio right click on the Oracle data source and select the "Re-Introspect Now" menu option. 

NOTE: Please test this in any of the test environments first and keep the backup of the current oracle_11g_oci_driver_values.xml. prior to making changes in production.