Node-oracledb: Support DCN ( Database Change Notification ) ?

[sagnol]

Is it support DCN ?

I looked for the some examples ( python, java ... )on oracle websites.

but I didn't see anything node.js DCN examples .

[cjbj]

There's no native support currently in node-oracledb. It's on our long list to evaluate (i.e. I'm not promising anything). You can use the PL/SQL APIs. @ dmcghan has an example, https://jsao.io/2015/02/real-time-data-with-node-js-socket-io-and-oracle-database/

[s130059660]

@dmcghan, just like always in his blogs, made a good job, but I have 2 problems with his example:

• It ties the application itself as these PLs need to be created for each application .
• It also ties application developers themselves with the database DBAs. This introduce problems like change request, approvals, etc.

However, I do understand oracle prioritizing some features over others.

I just wanted to cast my vote in favor of this feature as this feature eliminate the two problems above in an elegant way.

[cjbj]

Native support for CQN has been merged to the master branch, so it should appear in the future node-oracledb 2.3 release. Start with the development doc at https://github.com/oracle/node-oracledb/blob/master/doc/api.md#consubscribe and also see https://github.com/oracle/node-oracledb/blob/master/doc/api.md#cqn

There are a couple of examples: https://github.com/oracle/node-oracledb/blob/master/examples/cqn1.js and https://github.com/oracle/node-oracledb/blob/master/examples/cqn2.js

Note the standard CQN connection proviso: the DB must be able to resolve the node-oracledb host. This typically means having a hardcoded IP address for the node-oracledb machine.

[danilohgds]

@cjbj what if the node-oracledb host is behind a container, where the IP addresses are constantly changing, would CQN still work? I will try it out...just wondering if you have already tested this in docker envs.

[dmcghan]

@danilohgds We'd love for you to kick the tires and provide some feedback! 👍

where the IP addresses are constantly changing

I suspect you mean that the IP is randomly assigned when the docker container is started (rather than changing while it's running), is that correct?

CQN will try to determine the IP address automatically and use a randomly assigned port to send notifications. However, there are ipAddress and port parameters that allow you to explicitly define these values.

You can obtain the IP with a wrapper function on os.networkInterfaces, like this one: https://stackoverflow.com/questions/3653065/get-local-ip-address-in-node-js.

Also, I'd think you'd want to use a fixed port as you may need to ensure that the database server allows outbound traffic on that port.

[cjbj]

@danilohgds I didn't have any issues in my Docker configuration. I was using this as a template, so I had the DB in Docker as well. I didn't need to fiddle with ports, or set ipAddress in this case.

Install was helped by having some staged binaries - but you know how to do that
Let us know what you find.

[cjbj]

I put a simple Continuous Query Notification demo up at https://blogs.oracle.com/opal/demo-oracle-database-continuous-query-notification-in-nodejs

[cjbj]

Node-oracledb 2.3 is available on npm with Continuous Query Notification (which supercedes DCN) support: https://blogs.oracle.com/opal/node-oracledb-23-with-continuous-query-notifications-is-on-npm

[alpertandogan]

Native support for CQN has been merged to the master branch, so it should appear in the future node-oracledb 2.3 release. Start with the development doc at https://github.com/oracle/node-oracledb/blob/master/doc/api.md#consubscribe and also see https://github.com/oracle/node-oracledb/blob/master/doc/api.md#cqn

There are a couple of examples: https://github.com/oracle/node-oracledb/blob/master/examples/cqn1.js and https://github.com/oracle/node-oracledb/blob/master/examples/cqn2.js

Note the standard CQN connection proviso: the DB must be able to resolve the node-oracledb host. This typically means having a hardcoded IP address for the node-oracledb machine.

12C R2 DB able to resolve and connect using ACL with utl_http.request('xxx:8080') but CQN background job works with java it crashed when trigger CQN table

[dmcghan]

@alpertandogan

but CQN background job works with java it crashed when trigger CQN table

How do you know this? What errors do you see?

[cjbj]

@alpertandogan you already have #1014 open on this; please use that issue to follow up.