Node-oracledb: not able to execute multiple sql queries in one connection.execute() method.

[himanshushukla254]

I am fetching the values from an existing table for more than one items. I am using the following query
SELECT PROD_BAL from PROD_MASTER WHERE PID in (100857,100861)

I want to update this PROD_BAL for both the PID. can i use two queries for update in same connection menthod

[bagl]

You can create an anonymous PL/SQL block that performs your query and do the updates; then execute that block.

[dmcghan]

@himanshushukla254 Where do the PIDs in the set come from? There are 2 in your example. Will there always be 2? If not, what are the upper and lower bounds?

What, is the general process? Select the prod_bal (as you've demonstrated above) to bring the data to Node.js, do some calculations in Node.js, and then update the values in the database?

Have you considered doing this all in SQL or PL/SQL? The reason I suggest this is that Oracle Number and JavaScript numbers are not compatible (JavaScript doesn't support Decimal) so you could get rounding errors. SQL and PL/SQL are built to do these types of operations simply and accurately.

If you must do this in Node.js, consider using a library like decimal.js and bind the values to and from the database as strings to avoid the rounding errors there.

The more detail you provide us regarding what you're trying to do, the more we can help. I don't have enough yet to write any demos for you.

[himanshushukla254]

@dmcghan @cjbj 1. Yes the PID will be always two.

1. Yes The process is the same. I can do the operation in either node or sql ( no issue).

how can i execute this using node-oracledb

      `connection.execute(
      "UPDATE PROD_MASTER set PROD_BAL = 9000 WHERE PID = ' 100857'",
      "UPDATE PROD_MASTER set PROD_BAL = 7000 WHERE PID = ' 100861')",
      function(err, result)
      {
      });`

can i execute these two queries together like this. If not, how can i execute this ?

[dmcghan]

@himanshushukla254 We're still missing some info to help you effectively.

1. prod_bal comes from prod_master based on different two different PID values (HOW DO YOU KNOW WHICH PID VALUES TO UPDATE?)
2. some new prod_bal values are generated (WHERE DO THE VALUES 9000 and 7000 COME FROM?)
3. new prod_bal values are mapped back to prod_master based on PID values

For part 1, I'll assume some end user of the application has selected the PID values to update and passed them into the application.

As for part 2, I just need a general idea. Are they user-supplied values like I'm assuming for part 1? Do you apply a complex algorithm? If so, how complex is this? If it's simple, you could implement it in SQL without much effort (I'll assume a 10% increase for now).

Here's an example just to get the ball rolling. Please confirm that PID is a number, not a string.

Given this table:

create table prod_master (
  pid      number,
  prod_bal number,
  constraint prod_master_pk primary key (pid)
);

insert into prod_master (pid, prod_bal) values (100857, 5000);
insert into prod_master (pid, prod_bal) values (100861, 6000);

commit;

You could do this:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

// Assume these values came from an application or API
const pidValues = [100857, 100861];

async function updateProdBal() {
  let conn;

  try {
    // just for demo, normally conn would come from a pool
    conn = await oracledb.getConnection(config);

    let result = await conn.execute(
     `declare

        type number_aat is table of number
          index by pls_integer;

        l_pids number_aat;

      begin

        l_pids := :pids;

        -- assuming 10% increase in prod_bal for algorithm
        forall x in 1 .. l_pids.count
          update prod_master
          set prod_bal = prod_bal * 1.1
          where pid = l_pids(x);

        commit; -- saves a round trip with conn.commit() in JS

      end;`,
      {
        pids: {
          type: oracledb.NUMBER,
          dir: oracledb.BIND_IN,
          val: pidValues
        }
      }
    );

    console.log('Done');
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

updateProdBal();

[cjbj]

For the record, here's the link to the duplicate post: https://stackoverflow.com/questions/48022222/how-can-i-execute-multiple-sql-queries-with-node-oracledb-plugin-in-one-session with a suggested solution that nicely uses the SQL 'case' clause.

[cjbj]

Closing - no update