The ORACLE_UNHANDLED_NUMASCHAR statement controls how PowerExchange handles some numeric Oracle source columns.
If you enter Y, PowerExchange converts the following Oracle numeric datatypes:
NUMBER columns that have a precision greater than 28 or an undefined length are treated as variable-length strings instead of double-precision floating-point numbers.
FLOAT columns that have a precision greater than 15 significant digits are treated as variable-length strings.
PowerExchange uses the ORACLE_UNHANDLED_NUMASCHAR setting when creating capture registrations.
This statement applies to PowerExchange Express CDC for Oracle and PowerExchange Oracle CDC with LogMiner sources. You can use this statement to override PowerExchange default processing of numeric data to prevent data loss in certain circumstances. To override default processing, you must specify this statement prior to creating capture registrations.
Linux, UNIX, and Windows
. PowerExchange uses its default processing of Oracle NUMBER data. If you have NUMBER columns that have a precision greater than 28 or an undefined length, or if you have FLOAT columns with a precision greater than 15, change data loss might occur.
. PowerExchange handles NUMBER and FLOAT data in a manner that prevents data loss.
Default is N.
Enter this parameter with a value of Y before you create capture registrations for the Oracle source tables that contain the NUMBER or FLOAT columns. If you enter this parameter after the registrations exist, you must set the status of the registrations to History and then create the registrations again. Otherwise, change data loss might occur.
Oracle allows columns that have the NUMBER datatype to have their precision and scale determined by the numeric data that is written to the columns. Oracle supports a maximum precision of 38 and an exponent of +/-127.
If you do not explicitly define the precision and scale for NUMBER columns from which change data is captured, the following default PowerExchange and PowerCenter processing of change data can result in loss of precision and change data:
PowerExchange handles data in NUMBER columns that have an undefined length or a length greater than 100 bytes as double-precision floating-point numbers.
PowerCenter allows a maximum precision of 28 for decimal numbers.
To prevent change data loss with this type of data, enter Y for this statement and then create your capture registrations. PowerExchange registration processing can then handle numbers that have a precision greater than 28 as variable-length strings.
If you are writing the data to an Oracle target and want to maintain the precision as a variable-length string, edit the target definition to modify the column datatype. Within a PowerCenter mapping, you can convert a variable-length string to a number either implicitly by connecting to a numeric port or explicitly by using expressions. To avoid loss of precision in implicit conversions, you might need to edit the mapping to pass the data as a string from source to target.
PowerExchange supports the BINARY_DOUBLE and BINARY_FLOAT numeric datatypes by treating them as internal DOUBLE or FLOAT datatypes. However, PowerCenter converts BINARY_DOUBLE and BINARY_FLOAT datatypes to Oracle NUMBER(15) datatypes, which can result in arithmetic overflow and data loss.
In columns with an Oracle numeric datatype, PowerExchange treats the value of infinity as 0.