implement subscription to db events

vbuehringer

in the evolution from rest-services to websockets it should be possible to leverage the publish/subscribe functionality

inside oracle-db

a node program registering for db-events with callbacks would be the natural solution for reacting to events in the database and propagate

the changes via websockets to the webclients

the functionality is already in the oci-driver

danmcghan-Oracle

Hi vbuehringer,

While not as clean as registering event listeners directly via the driver, I covered a technique you may be able to use as a work around in my latest blog post: https://jsao.io/2015/02/real-time-data-with-node-js-socket-io-and-oracle-database/

The short of it is you could use UTL_HTTP to make callouts to your Node.js server(s) making them aware of any events.

I like the idea of registering events via the driver. Could you tell us more about what you have in mind? What type of events, just DB (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN) or DDL and DML? Or maybe integration with AQ?

Regards,

Dan

vbuehringer

integration with aq

like in the oci-example for registering with aq subscription

I do it with several queues and dbms_aq.dequeue, so I need one connection per queue

another possibilty is using dbms_aq.listen and then selection of the right queue

dbms_aq and node are good companions, the problem with aq is that every queue needs an oracle-process for listening

on the queue and is vulnerable to library cache locks due to always running procedures and dependencies

so I use an anonymous block in node to dequeue on several queues,( one connection for every queue )

the classical setup for node in combination with oracle (send out ) :

- triggers or procedures write in queue

- queue is dequeued in node and data sent out via restcall or websockets

(perhaps array dequeue and big messages )

the way into oracle with node rest-server:

- node writes in queue

- queue is dequeued in oracle and data processed (async )

perhaps queue-propagation from web database to transaction db for processing

queues everywhere, synchronous web-services stink, you need buffering of queues

Christopher Jones-Oracle

I'd love to see CQN  [1] and AQ [2] exposed natively in node-oracledb.  We've talked about them - they are definitely useful for application development.  Thanks for the use cases and votes for the features.

1. http://docs.oracle.com/database/121/ADFNS/adfns_cqn.htm#ADFNS018

2. http://docs.oracle.com/database/121/CNCPT/cmntopc.htm#CNCPT1715