node-oracledb multiple statement in one query

Question

In MySQL we can do the multiple statement in one query like below example. Is there any similar way to run multiple statement in one query in oracledb?

const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    multipleStatements: true
})

app.get('/distinct',(req,res)=>{  
    connection.query('select DISTINCT country FROM sample_data.olympic_winners order by country ASC; select DISTINCT sport FROM sample_data.olympic_winners order by sport ASC',(err,rows)=>{  
    if(!err)   
    res.send(rows);  
    else  
        console.log(err);  
      
})  
});

Christopher Jones · Answer 1 · 2021-11-07T09:01:55.423

Oracle only lets you execute one SQL or PL/SQL statement at a time.

You could wrap your queries in a PL/SQL block and use Oracle's Implicit Result Sets, see the doc:

For example:

const plsql = `
  DECLARE
    c1 SYS_REFCURSOR;
    c2 SYS_REFCURSOR;
  BEGIN
    OPEN c1 FOR SELECT city, postal_code
                FROM locations
                WHERE location_id < 1200;
    DBMS_SQL.RETURN_RESULT(c1);

    OPEN C2 FOR SELECT job_id, employee_id, last_name
                FROM employees
                WHERE employee_id < 103;
    DBMS_SQL.RETURN_RESULT(c2);
  END;`;

result = await connection.execute(plsql);
console.log(result.implicitResults);

will display:

[
  [
    [ 'Roma', '00989' ],
    [ 'Venice', '10934' ],
  ],
  [
    [ 'AD_PRES', 100, 'King' ],
    [ 'AD_VP', 101, 'Kochhar' ],
    [ 'AD_VP', 102, 'De Haan' ],
  ]
]

score 0 · Answer 2 · answered Nov 09 '21 at 17:46

Another approach would be to unite the queries on the database side in a single select statement without invocation of PL/SQL:

select DISTINCT 'country', country FROM sample_data.olympic_winners 
  UNION ALL
select DISTINCT 'sport', sport FROM sample_data.olympic_winners
  order by 1,2 ASC;

This approach requires identical row structures of the involved tables and delivers a compound resultset. It depends on what is the goal to be achieved with such a compound request, like the required structure of the resultset (just a plain unified list or rather hierarchical master-detail).

node-oracledb multiple statement in one query

2 Answers2