You’re offline. This is a read only version of the page.
13
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.
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:
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 👍.
#1
Michael E. Gernaey
566
Super User 2025 Season 1
#2
David_MA
516
Super User 2025 Season 1
#3
stampcoin
492
Share
Report
All responses (
Answers (
