Oracle SQL command not properly ended while running a subquery through Node.js

Question

I am trying to fetch all the columns present in a table with this subquery

I am calling my code with these parameters

let idsquery="select COLUMN_Name from ids_columns where table_id = (select table_id from ids_tables where table_name ='ZR_INVOICE_DETAILS');";
idsFunction(idsquery,icallback);

here is my code

const oracledb = require('oracledb');
const idsObj=require('../config').idsObj;
let error;
let user;
function idsconnection(query,callback){
  // var query="select COLUMN_Name from ids_columns where table_id=2016";
  console.log(query);
  oracledb.getConnection(
      idsObj,
      function(err, connection) {
        if (err) {
          console.log('failed to connect',err);
          error = err;
          return;
        }
        connection.execute(query, [], function(err, result) {
          if (err) {
            console.log('failed to execute',err);
            error = err;
            return;
          }
          // console.log('column names are',result.metaData);
          // console.log('rows are',result.rows);
          // console.log('rows count is',result.rows.length);

          connection.close(function(err) {
            if (err) {
              console.log('failed to close connection',err);
            }
            // console.log('callback is ',callback);
            callback(result)
          });
        })
      }
  );
}

module.exports=idsconnection;

this code works fine when I call it

let idsquery="select COLUMN_Name from ids_columns where table_id = 2012;";
idsFunction(idsquery,icallback);

like this

but it is giving this error when I execute the 1st query

failed to execute { [Error: ORA-00933: SQL command not properly ended] errorNum:933, offset: 125 }

Answer 1

As mentioned by @alex-poole in a comment, the issue (or first issue) will be that you have a trailing semi-colon in the statement:

let idsquery="select COLUMN_Name from ids_columns where table_id = (select table_id from ids_tables where table_name ='ZR_INVOICE_DETAILS');";

Change it to:

let idsquery="select COLUMN_Name from ids_columns where table_id = (select table_id from ids_tables where table_name ='ZR_INVOICE_DETAILS')";

Oracle SQL doesn't include semi-colons. Confusingly, semi-colons are needed for PL/SQL, and are also used for SQL by some tools like SQL*Plus to say "this is the end of the statement, execute everything before here".

A (potential) second issue is that you are not using bind variables. You probably want to do:

let query="select COLUMN_Name from ids_columns where table_id = (select table_id from ids_tables where table_name = :tn)";

connection.execute(query, ['ZR_INVOICE_DETAILS'], function(err, result) { . . . 

Bind variables improve scalability and help prevent SQL Injection security problems.