Answered

Best work around for date filter limitation for Oracle

(0) Share

Report

Posted on by plyons05

I am aware of the limitation on using a filter on a connection to Oracle and have determine a few possible work arounds, but looking for other suggestions or issues with my suggestions.

Scenario:

There is a transaction table in Oracle where we are pulling data over from to CDS. Since this Oracle table will always be growing, pulling over the entire table and looping over it every time will not be feasible from a time perspective.

We only need to pull over the records since the last time we pulled over records.

The flow would run every hour so only the records in the table from the last hour are needed.

Possible Work arounds

1. Create a stored procedure in Oracle and call that from flow to perform the date filtering

2. Pull the full table but sort it by newest records at the top. Loop through the table and evaluate each record's date. When we hit a record that does not match our date evaluation we can exit out of the loop.

Let me know if anyone has had issue with either of these or other suggestions.

Categories:

Using connectors

I have the same question (0)

All responses (2)

Answers (1)

Sort by

murshed Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @plyons05
There are many work arounds for the problems. You will need to try and see what works best for you. Since I don't have full understanding of the scale of the data you are working with my suggestion may not directly address your concern. Nonetheless, I will list few suggestions:

1. If your data has a index field you can use the index field instead of date. This will probably help speed up the query. But you will need to store the index separately.

2. You can you a Queue system to queue up the last update. Your process for CDS to Oracle will save data twice CDS -> Oracle and CDS -> Queue system of your choice. Instead of running your flow every hour you can have the Queue system trigger your flow when there is a new entry. This way flow runs only when there are new entries. Based on your need this can have a bit more overhead, since you are now maintaining another database system (Queue).

TBH, it sounds like the stored procedure might be the best option to use.

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION ☑️. If you find this reply helpful, please consider giving it a LIKE 👍.

Was this reply helpful? Yes No
Verified answer

plyons05 13 on at

Like (0)

Report
Copy link

Link copied!

The best option I've found, but not fully implemented is to use what's been suggested in other posts.
You can filter Oracle based on date, but not very dynamically.
So I split it up into a filtered Get Rows Oracle query and then a filter array with the more granular filter.

Step 1 filter:
year(CREATION_DATE) ge year(@{addDays(utcNow(), -1)}) and month(CREATION_DATE) ge month(@{addDays(utcNow(), -1)}) AND day(CREATION_DATE) ge day(@{addDays(utcNow(), -1)})

Step 2 format:
formatDateTime('1/12/2021 12:05 PM')

Was this reply helpful? Yes No