Incremental copy from onprem Oracle db

[cw88]

Hello, 

i tested an incremental load on an onprem oracle db, ended with following error message:

Spoiler

Failure happened on 'Source' side. ErrorCode=OracleReadError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to execute the query command during read operation.,Source=Microsoft.DataTransfer.Connectors.OracleV2Core,''Type=Oracle.ManagedDataAccess.Client.OracleException,Message=ORA-01861: literal does not match format string,Source=Oracle Data Provider for .NET, Managed Driver,'

The SQL query being executed on the Oracle side looks like this (with "MAP_TIME" configured as the incremental column):

Spoiler

SELECT * FROM "SCHEMA"."TABLE" WHERE "MAP_TIME" <= '2025-08-05T08:45:32'

When I run this query manually, I get the same ORA-01861 error. This leads me to the following questions:

1. Could this be a bug in the connector or configuration?
2. From a performance and compatibility perspective, is it advisable to compare a timestamp column with a string literal without explicit conversion?

Thanks in advance for your insights!

[cw88]

Hi @v-sathmakuri ,

i use ms fabric copy job (therefore opened under "copy job")

[v-aatheeque]

Hi @cw88 ,

Instead of relying on the auto-generated query, configure your source dataset in Fabric to use a custom SQL query that explicitly converts the string literal to a timestamp format:

SELECT * FROM "SCHEMA"."TABLE"
WHERE "MAP_TIME" <= TO_TIMESTAMP('2025-08-05T08:45:32', 'YYYY-MM-DD"T"HH24:MI:SS')

This ensures that Oracle correctly interprets the literal and avoids the ORA-01861 error.

Ensure that the column used for incremental load (MAP_TIME) is indeed of type TIMESTAMP or DATE. If it's a string or another type, conversion may need to be adjusted accordingly.

Hope this helps !!

[cw88]

Hi, 

how do i configure your suggestion "Instead of relying on the auto-generated query, configure your source dataset in Fabric to use a custom SQL query that explicitly converts the string literal to a timestamp format:" ?

When i configure a new item from type COPY JOB, select an oracle data source, i do not have the option to use a custom query?

[v-aatheeque · Accepted solution]

Hi @cw88 ,

Apologies for the trouble you’re running into. From the error,

• It looks like Fabric is treating your incremental column MAP_TIME as a date/time field.
• However, when it generates the filter, it passes the high-watermark value as a string in ISO 8601 format (2025-08-05T08:45:32).
• Oracle doesn’t understand the T in that format, which is why you see the ORA-01861 error.

Possible workarounds:

1. Switch to a Data Pipeline (Copy Activity):

• In Fabric, instead of a simple Copy job, create a Data pipeline.
• Use a Copy Data activity, where you can provide a source query with TO_TIMESTAMP(@pipeline().parameters.Watermark, 'YYYY-MM-DD"T"HH24:MI:SS').
• This gives you full control and fixes the error.

2. Change the column type(Source Table) :

• If MAP_TIME is a DATE/TIMESTAMP but you can expose a shadow column as formatted VARCHAR2 in Oracle (e.g., a view with TO_CHAR(MAP_TIME, 'YYYY-MM-DD"T"HH24:MI:SS')), then Copy job will match string-to-string.
• But this loses index efficiency and isn’t recommended for large tables.

Hope this  helps!!

Thank You.

[cw88]

Hi @v-aatheeque ,

thanks for your answer. 

So this is a bug in the copy job? 

The workaround using the copy activity will work. I hoped that using the copy job would require less effort...

[v-aatheeque]

Hi @cw88 

It’s not technically classified as a “bug,” but more of a known limitation in how the Oracle connector handles date/time literals during incremental loads. The auto-generated query doesn’t wrap the ISO 8601 string in a TO_DATE or TO_TIMESTAMP,causing that mismatch error.

Copy Job is designed for simplicity, but it doesn’t handle advanced scenarios like this well. If you’d prefer to stay with Copy Job, we suggest submitting this as product feedback (Fabric Ideas - Microsoft Fabric Community)

Hope it helps !!

Thank You.

[v-aatheeque]

Hi @cw88 
Thanks for raising this query !

It looks like your SQL query is trying to compare a timestamp column (MAP_TIME) with a date written as a plain string ('2025-08-05T08:45:32'). That can cause issues if the format of the string doesn’t match what the database expects.

• To fix this, you should convert the string into a proper timestamp using something like TO_TIMESTAMP, like this:
  
  
• SELECT * FROM "SCHEMA"."TABLE"

WHERE "MAP_TIME" <= TO_TIMESTAMP('2025-08-05 08:45:32', 'YYYY-MM-DD HH24:MI:SS')

•  Double-check the data type of the MAP_TIME column. If it’s a timestamp, you might need to ensure that your comparison value is also treated as such.

As for whether this is a bug in the connector or setup it’s probably not. It’s more likely just a formatting mismatch. Using the right conversion functions helps avoid compatibility issues.

Hope this helps !!

[cw88]

Hello @v-aatheeque ,

Unfortunately, the query you're referring to is not manually written by me. It’s automatically generated by the system when configuring the copy activity with incremental load enabled. In other words, it’s system-generated and not something I can directly influence.

[v-sathmakuri]

Hi @cw88 ,

Sorry for the delayed response. Could you let us know if you're trying to access the Oracle database from Azure Data Factory pipelines or from Fabrics pipelines?

Thanks!!