Forum Discussion

cw88's avatar
cw88
Icon for Helper IV rankHelper IV
1 year ago
Solved

Incremental copy from onprem Oracle db

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!

Copy job
Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

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

     

9 Replies

  • v-aatheeque's avatar
    v-aatheeque
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
      cw88
      Icon for Helper IV rankHelper IV
      1 year ago

      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's avatar
        v-sathmakuri
        Icon for Community Support rankCommunity Support
        1 year ago

        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!!

    • v-aatheeque's avatar
      v-aatheeque
      Icon for Community Support rankCommunity Support
      1 year ago

      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's avatar
        cw88
        Icon for Helper IV rankHelper IV
        1 year ago

        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?