1

I was wondering if it's possible to keep pinging a oracle table until a row is added to a table or removed.

I want to start the next action after I get a confirmation that the row has been added or removed. Preferred method is a query that keeps pinging until it happens. Normally when querying I use executeQuery method but that only return when it has results

I haven't found a method yet that is able to do that.

So does anyone know a way to do this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Eve
  • 514
  • 3
  • 12
  • 23
  • 1
    Please describe what you want to do in more detail. 'Until a row is added or removed' - what row, what table, by which process? Do you want your application to be notified about every change in database? – socha23 Dec 12 '11 at 11:05
  • You haven't found a method that is able to do that, because you need to write this method... Basically have a thread that runs select queries on your table every XX seconds until it finds what you're looking for... – Guillaume Dec 12 '11 at 11:06
  • Have a look at these questions: http://stackoverflow.com/questions/2348416/database-polling-using-java http://stackoverflow.com/questions/787073/alternative-to-polling-database – Guillaume Dec 12 '11 at 11:11
  • Do I understand correctly that you want to execute your query asynchronously, do some other things before it returns, and start next action after it returns? It would help greatly if you could provide a real-world use case. – socha23 Dec 12 '11 at 11:33

2 Answers2

4

In Oracle 11g you could use Database Change Notification. Instead of polling the database to check for changes, you register to receive an event when a change occurs. There is a source code example here.

If you choose to poll instead, then you should consider your query carefully. For example, relying on number of rows in the table - SELECT COUNT(*) FROM your_table - could give you the wrong answer if the same number of rows were added and removed from the table between two executions of your query.

ewan.chalmers
  • 16,145
  • 43
  • 60
0

I'm not familiar with Oracle, so I will suggest you a database independent approach.

Do you delete or "inactivate" records in your tables? If you "inactivate", you could maintain a "Version" column in every row, ++'ing in every CUD operation. For example, when you create a new row, it's version is 1. After the first update is 2 and so it goes on.

That way, instead of relying on SELECT COUNT(*) ..., you could rely on SELECT SUM(VERSION) .... ;)

You could also work with an Audit Trail - auditing every CUD operation in every table -, and poll the audit trail instead of the tables.

everton
  • 7,579
  • 2
  • 29
  • 42
  • not sure if we're necessarily looking for a database-independent solution, as the question is tagged 'Oracle'. – socha23 Dec 12 '11 at 12:02
  • I know that, I've just said that because I'm not familiar with oracle. Consider that comment a "loud thought" :P – everton Dec 12 '11 at 12:11