Node-oracledb: CQN not working with oracledb NodeJs

[raaedserag]

1.Versions

Oracle Database 11g Express Edition     11.2.0.2.0
platform: linux
Node: v10.18.1
arch: x64
oracledb: 5.0.0
oracle-client: 19.5.0.0.0

1. Non-working function

2. I have tried to run the cqn script exactly like the example shown in cqn1 example
   The Oracle database and Node.Js server running on the same machine & I'm pretty sure that I've grant CONTINOUS NOTIFICATION privilege for the system user I'm using
   

   1. Scripts:

   • First, here's the query I've used to make sure that my user has the CHANGE NOTIFICATION privilege:
     SELECT * FROM USER_SYS_PRIVS WHERE PRIVILEGE= 'CHANGE NOTIFICATION'

     USERNAME PRIVILEGE ADMIN_OPTION
xxx CHANGE NOTIFICATION YES

   • And here's the Node.Js script I've used as the example said:

   const oracledb = require("oracledb");
   const dbConfig = {
     user          : "xxx",
     password      : "xxx",
     connectString : "localhost/XE",
     externalAuth: false,
     events: true
   };
   
   const interval = setInterval(function() {
     console.log("waiting...");
   }, 5000);
   
   function myCallback(message)
   {
     console.log("CQN Triggered")
   }
   
   const options = {
     callback : myCallback,
     sql: `SELECT * FROM no_cqntable WHERE k > :bv`,
     binds: { bv : 100 },
     timeout: 600,
     qos : oracledb.SUBSCR_QOS_QUERY | oracledb.SUBSCR_QOS_ROWIDS
   };
   
   async function setup(connection) {
     const stmts = [
       `DROP TABLE no_cqntable`,
   
       `CREATE TABLE no_cqntable (k NUMBER)`
     ];
   
     for (const s of stmts) {
       try {
         await connection.execute(s);
       } catch(e) {
         if (e.errorNum != 942)
           console.error(e);
       }
     }
   }
   
   async function runTest() {
     console.log("Server Started")
     let connection;
   
     try {
       connection = await oracledb.getConnection(dbConfig);
   
       await setup(connection);
   
       await connection.subscribe('mysub', options);
   
       console.log("Subscription created...");
   
     } catch (err) {
       console.error(err);
       clearInterval(interval);
     } finally {
       if (connection) {
         try {
           await connection.close();
         } catch (err) {
           console.error(err);
         }
       }
     }
   }
   
   process
     .on('SIGTERM', function() {
       console.log("\nTerminating");
       process.exit(0);
     })
     .on('SIGINT', function() {
       console.log("\nTerminating");
       process.exit(0);
     });
   
   runTest()
   

   • Finally, After running the server I'm executing this insertion statement waiting for the trigger, but it doesn't happen

   INSERT INTO NO_CQNTABLE VALUES (101);
   COMMIT;
   

[cjbj]

My late-night-hint-without-looking-at-the-code would be try a recent DB version. Oracle XE 18 is available. Oracle 11g came out in 2009.

[anthony-tuininga]

Are the client and database on the same machine? If not, the database has to be able to connect back to the client in order for CQN to work -- at least until the "clientInitiated" option is available. See the documentation. That option isn't available until 19.4 so you'll have to ensure that the database server and client can communicate in both directions.

[raaedserag]

My late-night-hint-without-looking-at-the-code would be try a recent DB version. Oracle XE 18 is available. Oracle 11g came out in 2009.

I've updated the DB driver to Oracle Database 12c Enterprise Edition 12.2.0.1.0
but it still not working, so should I upgrade to XE 18 !?
No way to working with 12c?

[raaedserag]

Are the client and database on the same machine? If not, the database has to be able to connect back to the client in order for CQN to work -- at least until the "clientInitiated" option is available. See the documentation. That option isn't available until 19.4 so you'll have to ensure that the database server and client can communicate in both directions.

I've tried to ping between the machines in both directions and it has succeeded, so how I can ensure more about the communication?

[anthony-tuininga]

A simple ping uses a different port than the one that the Oracle Database will want to use. You can control which port and which IP address to use (if there are multiple on the machine) by specifying that option when you create the subscription. See the documentation for details. @cjbj may know how to use server side tracing to find out what the problem is.

[raaedserag]

My late-night-hint-without-looking-at-the-code would be try a recent DB version. Oracle XE 18 is available. Oracle 11g came out in 2009.

I've updated the DB driver to Oracle Database 12c Enterprise Edition 12.2.0.1.0
but it still not working, so should I upgrade to XE 18 !?
No way to working with 12c?

[raaedserag]

Are the client and database on the same machine? If not, the database has to be able to connect back to the client in order for CQN to work -- at least until the "clientInitiated" option is available. See the documentation. That option isn't available until 19.4 so you'll have to ensure that the database server and client can communicate in both directions.

Tha

[raaedserag]

@cjbj @anthony-tuininga
Thank you all, it worked
I've done 2 things:

• first, I upgraded the DB driver to 12.2.0.1 (12 c)
• then, I had a mess in my network connections between the server machine and DB machine, so when I solved it and 2 devices could communicate in both directions, it worked well