Forum Discussion

eleonb's avatar
eleonb
Frequent Visitor
8 months ago

Copy data Oracle Timezone Issue

Context / Background We are using Fabric to copy data from an internal Oracle database via an On-Premises Data Gateway. The Oracle database has TZFILE version 14 (timezlrg_14.dat). I do not have th...
data engineering
Data Pipeline
Zanqueta's avatar
Zanqueta
Super User
8 months ago

 

 

 

This is a known issue when connecting to Oracle via ODP.NET in environments where the timezone region mapping is not fully supported by the Oracle client or the gateway configuration. The error:
ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
occurs because Oracle tries to resolve the timezone region from the client session, and the region is not recognised by the database's TZFILE version.

Why Dataflows Work but Pipelines Fail

  • Dataflows use Power Query connectors that handle timezone differently (often defaulting to UTC).
  • Pipelines with Copy Data rely on the ODP.NET driver and pass the client timezone region, which Oracle cannot resolve.

✅ Workarounds

Since you cannot modify the database, you need to adjust the client-side behaviour:

Option 1: Force UTC in the Connection

Add the following to your connection string:
;Connection Timeout=30;Validate Connection=true;DefaultTimeZone=UTC
or explicitly set:
ALTER SESSION SET TIME_ZONE = 'UTC';
You can run this as a Pre-copy script in the Copy Data activity:
 
 
 
ALTER SESSION SET TIME_ZONE = 'UTC';
 
This avoids Oracle trying to resolve the region from the client.

Option 2: Use UseTZRegion=false in ODP.NET Config

You mentioned trying this, but ensure:
  • The file oracle.manageddataaccess.client.config is in the same directory as the gateway executable.
  • It contains:
 
<configuration>
  <oracle.manageddataaccess.client>
    <version number="*">
      <setting name="UseTZRegion" value="false"/>
    </version>
  </oracle.manageddataaccess.client>
</configuration>
 
Restart the gateway after applying this.

Option 3: Upgrade Oracle Client

If possible, use ODP.NET Managed Driver v21 or later, as older versions have known issues with TZFILE compatibility.

Why Groups or Service Principals Are Not Relevant Here

This is purely a timezone resolution issue, not an authentication problem.
 
Reference:
 

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

 
eleonb's avatar
eleonb
Frequent Visitor
8 months ago

Thanks for the response! 

After extensive testing, it looks like this is a limitation of the OracleV2 driver used by Fabric Copy Activity.

The activity copy data does not allow pre-copy scripts, does not respect ODP.NET configuration files, and does not use the Oracle client installed on the gateway , I suspect Fabric use its own driver located in the datagateway server in FabricIntegrationRuntime\...\sxs\OracleV2Unbundle. Also in the connection configuration I cannot include the options you mention in the connection string, given that Fabric only let me put the Oracle Descriptor inside the Server field when creating the connection.

As a result, there is no supported way to force UTC or disable timezone region mapping when connecting to Oracle databases using TZFILE 14.

Dataflows work because they use a different connector that handles timezones differently as you mention.