3 Replies Latest reply on Mar 4, 2015 8:02 PM by cj

    implement subscription to db events


      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

        • 1. Re: implement subscription to db events

          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?




          • 2. Re: implement subscription to db events

            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

            • 3. Re: implement subscription to db events

              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