2

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 }
5
  • 1
    Are you sure the code you said does work is exactly as you showed - including the semicolon at the end? I don't think that should be there, in either version, and it would cause that error in JDBC, dynamic SQL etc. calls - but not sure about Node. Commented Feb 11, 2019 at 13:22
  • The code you say works wouldn't actually work with the semi-colon at the end of the SQL statement. After you remove the semi-colon, which is causing the current error your seeing, you'll need to change the equality operator to use either 'in' or 'exists'. I prefer 'in', but be careful with null values and 'in'. Commented Feb 11, 2019 at 23:26
  • @AlexPoole thank you so much for the comment , I removed the semicolon and it worked. Commented Feb 12, 2019 at 4:56
  • @DanMcGhan I don't need to use IN , cause I know my query will definitely return a unique value. Commented Feb 12, 2019 at 5:05
  • @pranaytanniru Ah, sorry. If it's a single row fetch then you're good. :) Commented Feb 12, 2019 at 14:34

2 Answers 2

2

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.

Sign up to request clarification or add additional context in comments.

Comments

2

Query itself looks OK. The error mentions "offset: 125" which points to the closing parenthesis.

Would it help if you rewrite that query so that it uses join (and avoid subqueries), e.g.

let idsquery="SELECT column_name FROM ids_columns c JOIN ids_tables t ON c.table_id = t.table_id WHERE t.table_name = 'ZR_INVOICE_DETAILS';";

1 Comment

No, it didn't work, Its giving the same error when I tried the query you mentioned.a I removed the semi colon in your query and it worked. But thanks to you , I learnt how to rewrite the query using joins.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.