CODEWITHSUNDEEP
node.jsnode-mysqlnode-oracledbnode-mysql2
faisal-akbar
faisal-akbar

Reputation: 63

node-oracledb multiple statement in one query

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);  
      
})  
});

Upvotes: 0

Views: 1082

Answers (2)

cloudrunnr
cloudrunnr

Reputation: 1

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).

Upvotes: 0

Christopher Jones
Christopher Jones

Reputation: 10556

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' ],
  ]
]

Upvotes: 1

Related Questions