How To Fix Azure Data Factory Oracle 2.0 Connector CLR Error with SDO

Select Language:

If you’ve recently upgraded your Oracle Linked Service connection from version 1.0 to 2.0 and are experiencing issues with loading data, particularly with columns that use the Oracle SDO_GEOMETRY data type, here’s a simple way to fix it.

In the previous version (1.0), queries involving these spatial columns worked smoothly, converting the data into string format without any problems. But after switching to version 2.0, you might encounter an error like this:

“ErrorCode=DataTypeNotSupported, ‘Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=Not supported clr type: Object., Source=,’”

This error shows that the new connector doesn’t support the SDO_GEOMETRY data type directly. It’s also common to use the property supportV1DataTypes = true to handle various NUMBER columns that have different scales and precisions, especially when exporting data to formats like Parquet. But this doesn’t seem to fix the issue with spatial data types.

Here’s what you can do to resolve the problem:

Use a query to convert the SDO_GEOMETRY data into a string before loading it. For example, use SDO_UTIL.TO_WKTHEXPRESSION() or SDO_UTIL.TO_WKTGEOMETRY() functions in your SQL statement to convert spatial data into a readable WKT (Well-Known Text) format. This way, you’re pulling the geometry as text instead of a complex object.
Modify your source query to apply this conversion. For example:

sql
SELECT
column1,
column2,
SDO_UTIL.TO_WKTHEXPRESSION(spatial_column) AS spatial_text
FROM your_table;

Ensure your data flow or pipeline uses this new query. This approach helps bypass the compatibility issue related to the unsupported data type.
If you have multiple spatial columns, consider applying the conversion to all of them. This method keeps your data pipeline flexible and avoids hardcoding specific scale or precision details for NUMBER columns.

Remember, this solution works because it turns the complex spatial data into a simple string, which the 2.0 connector can easily handle. It’s especially handy when working with Oracle 19c and trying to keep your data flow running smoothly after upgrades.