Azure Data Factory Oracle Source with date variable-CodePudding

I am building a logical data warehouse. Source is Oracle and Destination is Data Lake. Initial load is done with ADF reading data from On Prem Oracle server and Parquet files created to serve as database. Now I need to add functionality of incremental data loading.

1- I have added a Lookup to start with, in which LDW is queried to get the last date.

2- Used a Set Variable activity to set the variable with the date

3- Copy Activity to read data starting from the date 1 and up to SYSDATE -1. This task is failing as I am unable to build the where the clause properly ( I am new to ADF). My current Where clause is this

WHERE  TO_DATE(si.schedule_date, 'YYYYMMDD') 
BETWEEN to_date('formatDateTime('@{variables('LastDate')}', 'yyyyMMdd')','YYYYMMDD') 
AND  SYSDATE-1

Error I am getting is this:

{
    "errorCode": "2200",
    "message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00907: missing right parenthesis,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00907: missing right parenthesis,Source=msora28.dll,'",
    "failureType": "UserError",
    "target": "Copy data1",
    "details": []
}

Any help will be highly appreciated. I am stuck on this for quite long and trying to get out of confusion. Thanks

CodePudding user response：

There seems to be a missing parenthesis. Make sure you close the parenthesis rightly.

And , I'm assuming the schedule date is a date column.. try this out ?

@Concat('select part of query ', 'WHERE si.schedule_date BETWEEN ' , formatDateTime({variables('LastDate')}, 'yyyyMMdd') , ' AND SYSDATE-1')

CodePudding user response：

The error indicates that there is some syntax error after ADF parsing your oracle query.

You may want to try something like this:

WHERE TO_DATE(si.schedule_date, 'yyyymmdd') 
BETWEEN TO_DATE('@{variables('LastDate')}', 'yyyymmdd') AND SYSDATE-1

Make sure @{} is associated with variables('LastDate'); and the expression is single quoted