Dynamically query Oracle table by date with Azure Data Factory

Question

I am creating a copy job with Azure Data Factory (v2) from our on-premise Oracle database to our Azure Data Lake. Ideally, this copy job is set up as a delta-load, where only the information from the last day is considered.

To do so, we want to filter the column "load_time", which is of the format datetime, with dynamic content functionality of Azure Data Factory.

The dummy query would be:

SELECT sales.* FROM schema.sales sales WHERE sales.load_time >= {everything from one hour ago}

When enriching this query with dynamic content, we have something like

SELECT sales.* FROM schema.sales sales WHERE sales.load_time >= addHours(utcnow(), -1, format='yyyy-MM-dd HH:mm:ss')

However, we continue to run into an error:

ORA-00904:"ADDHOURS": invalid identifier

Does anyone have any experience / insight in what is going wrong here?

Thanks

EDIT-1: We aim to use the expressions from the Dynamic Content in Azure Data Factory, such as "addHours" to set the datetime limit.

Answer 1

Looks like the issue is with Dynamic expression which is used to form the required query.

Please try updating your dynamic expression as below in your ADF pipeline activity to form a valid query.

I have defined a pipeline parameter named SingleQuotes of type string and value = ' (single quote) - This is required to form a valid where condition like "WHERE sales.load_time >='2020-03-16 20:04:04' "

Dynamic Expression:

@concat('SELECT sales.* FROM schema.sales sales WHERE sales.load_time >=', pipeline().parameters.singleQuotes, formatDateTime(addHours(utcnow(), -1), 'yyyy-MM-dd HH:mm:ss'), pipeline().parameters.singleQuotes)

This dynamic expression will generate a SQL query as below: (I have tried this in T-SQL)

"sqlReaderQuery": "SELECT sales.* FROM schema.sales sales WHERE sales.load_time >='2020-03-16 20:04:04'"

Hope this helps.