Home » SQL & PL/SQL » SQL & PL/SQL » Fetching changes for a given query
Fetching changes for a given query [message #395123] Tue, 31 March 2009 07:02 Go to next message
Messages: 32
Registered: March 2009
I have an application that uses a query to retrieve thousands of
Initially, the application fetches all the rows. After that, the application
has to fetch only the changed rows. This can be done by using a date
column that contains the Last Update Time and adding a date condition to
the query. Something like (pseudo code):

Select * from myTbl t
where t.name like '%john%'
and lastUpdateTime > current_date - (5 seconds)

In the solution above, the application gets the updated rows (in the last 5
seconds). But the application does not know which rows have been deleted
and which rows don't meet the condition.
In the given example, initially, the application has all the rows, then, when
trying to get the changes, the application don't get a row that its "name"
has been changed to 'foo'. Thus the app still displays this rows and doesn't
know that this row is not valid due to the name change (The row doesn't
meet the query condition).

Do you have any idea how to get correct changes for a given query periodically?
Re: Fetching changes for a given query [message #395130 is a reply to message #395123] Tue, 31 March 2009 07:26 Go to previous messageGo to next message
Messages: 12820
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you had a look at oracle's Database Change Notification mechanism?

It's a different approach to what you're doing, one I keep meaning to find the time to have a play with, so I'm not sure if it'll cover all your needs though I assume it should.
Re: Fetching changes for a given query [message #395161 is a reply to message #395123] Tue, 31 March 2009 09:07 Go to previous message
Michel Cadot
Messages: 65144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to go to Database Change Notification, carefully check the drawbacks (and restrictions): server load increase, sga usage...

I recommend you to use the server PL/SQL callback procedure to fit a table with changes so that you application can query this table instead of the original one.
I recommend you to NOT use client side callback.

Previous Topic: Problem in uploading file
Next Topic: Maximum Value Wihtout using Oracle Functions
Goto Forum:

Current Time: Sun Aug 20 23:48:52 CDT 2017

Total time taken to generate the page: 0.01446 seconds