Observer pattern in Oracle

Question

Can I set hook on changing or adding some rows in table and get notified someway when such event araised? I discover web and only stuck with pipes. But there is no way to get pipe message immediately when it was send. Only periodical tries to receive.

Answer 1

Implementing an Observer pattern from a database should generally be avoided.

Why? It relies on vendor proprietary (non-standard) technology, promotes database vendor lock-in and support risk, and causes a bit of bloat. From an enterprise perspective, if not done in a controlled way, it can look like "skunkworks" - implementing in an unusual way behaviour commonly covered by application and integration patterns and tools. If implemented at a fine-grained level, it can result in tight-coupling to tiny data changes with a huge amount of unpredicted communication and processing, affecting performance. An extra cog in the machine can be an extra breaking point - it might be sensitive to O/S, network, and security configuration or there may be security vulnerabilities in vendor technology.

If you're observing transactional data managed by your app:

• implement the Observer pattern in your app. E.g. In Java, CDI and javabeans specs support this directly, and OO custom design as per Gang Of Four book is a perfect solution.
• optionally send messages to other apps. Filters/interceptors, MDB messages, CDI events and web services are also useful for notification.

If users are directly modifying master data within the database, then either:

• provide a singular admin page within your app to control master data refresh OR
• provide a separate master data management app and send messages to dependent apps OR
• (best approach) manage master data edits in terms of quality (reviews, testing, etc) and timing (treat same as code change), promote through environments, deploy and refresh data / restart app to a managed shedule

If you're observing transactional data managed by another app (shared database integration) OR you use data-level integration such as ETL to provide your application with data:

• try to have data entities written by just one app (read-only by others)
• poll staging/ETL control table to understand what/when changes occured OR
• use JDBC/ODBC-level proprietary extension for notification or polling, as well mentioned in Alex Poole's answer OR
• refactor overlapping data operations from 2 apps into a shared SOA service can either avoid the observation requirement or lift it from a data operation to a higher level SOA/app message
• use an ESB or a database adapter to invoke your application for notification or a WS endpoint for bulk data transfer (e.g. Apache Camel, Apache ServiceMix, Mule ESB, Openadaptor)
• avoid use of database extension infrastructure such as pipes or advanced queuing

If you use messaging (send or recieve), do so from your application(s). Messaging from the DB is a bit of an antipattern. As a last resort, it is possible to use triggers which invoke web services (http://www.oracle.com/technetwork/developer-tools/jdev/dbcalloutws-howto-084195.html), but great care is required to do this in a very coarse fashion, invoking a business (sub)-process when a set of data changes, rather than crunching fine-grained CRUD type operations. Best to trigger a job and have the job call the web service outside the transaction.