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.
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 🌀.
