cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
plyons05
Regular Visitor

Best work around for date filter limitation for Oracle

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.

1 ACCEPTED SOLUTION

Accepted Solutions
plyons05
Regular Visitor

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')

 

plyons05_0-1612561750954.png

 

View solution in original post

2 REPLIES 2
murshed
Microsoft
Microsoft

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

plyons05
Regular Visitor

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')

 

plyons05_0-1612561750954.png

 

View solution in original post

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Kudoed Authors
Users online (1,699)