Forum Stats

  • 3,826,604 Users
  • 2,260,675 Discussions
  • 7,897,026 Comments

Discussions

implement subscription to db events

vbuehringer
vbuehringer Member Posts: 25
edited Mar 4, 2015 3:02PM in Node.js

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

900566

Comments

  • danmcghan-Oracle
    danmcghan-Oracle Member Posts: 43 Employee
    edited Mar 3, 2015 2:53PM

    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
    vbuehringer Member Posts: 25
    edited Mar 4, 2015 2:32PM

    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
    Christopher Jones-Oracle Member Posts: 1,648 Employee
    edited Mar 4, 2015 3:02PM

    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

    900566