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.

Kranthi Pakala · Accepted Answer · 2020-03-16 21:17:13Z

3

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.

answered Mar 16, 2020 at 21:17

Kranthi Pakala

1,4627 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bramb Over a year ago

Do I understand your solution correctly that @CAST is needed to convert the Dynamic Content to a string, so that the Oracle DB understands the query?

bramb Over a year ago

Thank you for your help! We were able to progress and found a new error, namely: "ORA-01861: literal does not match format string". Any leads on this?

bramb Over a year ago

We were able to solve the error by using a TO_CHAR around "sales.load_time" in the WHERE statement.

kevinskio · Accepted Answer · 2020-03-16 16:49:45Z

1

I believe the ADDHOURS that is in use is from Oracle .Net C# function. It's not part of the Oracle functions. You could build your own function in PL/SQL which mimics the same function but the better solution is to rewrite the where clause. Something like

WHERE sales.load_time >= sysdate - interval '1' hour

answered Mar 16, 2020 at 16:49

kevinskio

4,5711 gold badge27 silver badges42 bronze badges

1 Comment

bramb Over a year ago

The ADDHOURS is function from Azure Dynamic Content Documentation, which we are trying to use in the Azure Data Factory pipeline.

Ravi kumar · Accepted Answer · 2023-04-20 10:31:56Z

0

I had the same issue I have sorted it out via the following method:

Create a Set variable activity (Let's say the name - X)

@formatDateTime('2022-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')

Write the following Code to pipeline expression builder:

@{concat('select * from Table where Column1=''','A',''' AND TO_CHAR(Date, ''', 'YYYY-MM-DD HH24:MI:SS',''') >= ''', variables('X'),'''')}

This will do the trick!

answered Apr 20, 2023 at 10:31

Ravi kumar

1716 bronze badges

Collectives™ on Stack Overflow

Dynamically query Oracle table by date with Azure Data Factory

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related