ORA-00900: invalid SQL statement - when run a query using node-oracledb

Question

I logged in as user AN and created a procedure in sql developer:

CREATE OR REPLACE PROCEDURE **viewSystemUsers**
AS 
  sysRefCursor SYS_REFCURSOR;
BEGIN
  OPEN sysRefCursor
  FOR 
    SELECT USERNAME, USER_ID, PASSWORD FROM dba_users;
    dbms_sql.return_result(sysRefCursor);
END;

And executed it, this worked normally. After that i successfully connected as the same user using node-oracledb:

  oracle.getConnection(
        {
            user : "AN",
            password: "AN123",
            connectString: "localhost:1521/orcl"
            
        }, 
        (error, **connection**) => {
                if(error){
                    console.error(error.message);
                    return;
                }
                else
                    console.log('connect sucessfully!');

But when i set the EXECUTE query:

**connection**.execute(
      `execute **viewSystemUsers**;`,
       (err, result) => {
            if(err){
                  console.error(err);
                  return;
            }
           console.log(result);

I received [Error: ORA-00900: invalid SQL statement] { errorNum: 900, offset: 0 }. Can anyone help me to fix it? Thank you very much.

Does node-oracledb support dbms_sql.return_result? If it does not then you may need to use an OUT parameter on the procedure (which would be the typical way of achieving the same result). — MT0
@MT0 do you mean using a sys_refcursor variable as an out parameter, calling the proc the printing that variable? — Nguyen Bot
Use the SYS_REFCURSOR variable as an OUT parameter, call the procedure and then loop through the variable to get the result set. — MT0
@M10, does node-oracledb support dbms_ouput.putline()? If it doesn't, how to get data from the loop? — Nguyen Bot

Christopher Jones Christopher Jones · Accepted Answer · 2021-04-01T22:42:56

You are passing the string execute ... to the database. But execute is not a SQL keyword - it is a SQL*Plus command. So the database doesn't understand it and gives an error. Instead do begin viewSystemUsers() end;.
For ease of programming use async/await, not callbacks
Review the node-oracledb example impres.js becuase this shows the use of Implicit Results (which is what dbms_sql.return_result() is).
Review the node-oracledb example plsqlproc.js.

You code could be like:

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

if (process.platform === 'darwin') {
  oracledb.initOracleClient({libDir: process.env.HOME + '/Downloads/instantclient_19_8'});
}

let sql, binds, options, result;
sql = `begin viewSystemUsers(); end;`;
binds = [];
options = { outFormat: oracledb.OUT_FORMAT_OBJECT };

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(dbConfig);

    result = await connection.execute(sql, binds, options);
    console.dir(result, { depth: null });

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();

ORA-00900: invalid SQL statement - when run a query using node-oracledb

1 Answers