Generic Oracle (Edge)

Last updated on Dec 13, 2024

Edge Pipeline is currently available under Early Access. You can request access to evaluate and test its features.

Oracle database is a cross-platform Relational Database Management System (RDBMS) that can run on various hardware across operating systems, including Windows Server, Unix, and various distributions of GNU/Linux. Oracle is used to store and manage both small and large datasets with optimal speed, efficiency, and security.


Data Type Mapping

Hevo maps the Oracle Source data type internally to a unified data type, referred to as the Hevo Data Type, in the table below. This data type is used to represent the Source data from all supported data types in a lossless manner.

The following table lists the supported Oracle data types and the corresponding Hevo data type to which they are mapped:

Oracle Data Type Hevo Data Type
- BIT BOOLEAN
- NUMBER
- FLOAT
DECIMAL
- BINARY_FLOAT
- BINARY_DOUBLE
FLOAT
- CHAR
- VARCHAR
- VARCHAR2
- NCHAR
- NVARCHAR
- ROWID
- INTERVAL_DAY_TO_SECOND
- INTERVAL_YEAR_TO_MONTH
VARCHAR
- TIMESTAMPTZ
- TIMESTAMP_LOCALTZ
TIME_TZ
- DATE DATE
- TIMESTAMP TIME

Read ANSI, DB2, and SQL/DS Data Types to know the data types that Oracle converts from ANSI to its supported ones.

Handling of NUMBER Data Type

In Oracle, NUMBER is a data type that stores fixed or floating-point numbers. To keep a check on the integrity of the input, the NUMBER data type is specified in the format NUMBER (p,s), where s is the scale and p is the precision. Precision (p) refers to the maximum number of significant digits a number can have. Significant digits are the digits in a number carrying meaningful information and exclude leading or trailing zeros. Scale (s) refers to the number of digits to the right of the decimal point. Read Numeric Data Types to know more about how Oracle handles the NUMBER data type.

Note: Hevo does not support NUMBER data types with a negative scale.

Hevo calculates the width of a stored value using the formula, Width = Precision - Scale. Width refers to the total number of digits required to store a number, considering both the integer (digits before decimal point) and fractional (digits after decimal point) parts.

The NUMBER data types are mapped to the following Hevo data types based on the calculated width:

Width Hevo Data Type
< 5 SHORT
< 10 INT
< 19 LONG
> 19 DECIMAL

Note: If precision and scale are not defined for the NUMBER data type, Destination-specific default maximum values for precision and scale are used.

Handling of Unsupported Data Types

If any of the Source objects contain data types that are not supported by Hevo, they are marked as unsupported during object configuration in the Pipeline.

At this time, the following Oracle data types are not supported by Hevo:

  • CLOB
  • NCLOB
  • BLOB
  • UROWID
  • LONG
  • RAW
  • LONG RAW
  • XMLTYPE
  • BFILE
  • Any other data type not listed in the tables above.

Source Considerations

  • Redo Log does not support user-defined data types. Therefore, fields with such data types are not captured in the log and are lost.

  • Hevo uses the Oracle data dictionary to translate redo logs during log switches. When Oracle LogMiner reads a log entry, it refers to the Oracle data dictionary to understand and process the logs. This approach requires minimal database resources but it does not support tracking schema changes.

Note: If your use case involves frequent schema changes and you want Hevo to write the Oracle data dictionary to redo logs to track the schema changes, contact Hevo Support.


Limitations

  • Hevo does not set the metadata column __hevo_is_deleted__ to True for data deleted from the Source table using the TRUNCATE command. This action could result in a data mismatch between the Source and Destination tables.

Tell us what went wrong