how to properly quote an Oracle SQL string in JavaScript

Question

In my node.js app I need to run an Oracle query as follows:

var last_reported_date = '2016-05-09 18:16:59';

 var query = 'SELECT "HpdHelpdesk".INCIDENTNUMBER,' +
        '"HpdHelpdesk".SUBMITTER,' +
        '"HpdHelpdesk".REPORTEDDATE,' +
        '"HpdHelpdesk".LASTRESOLVEDDATE,' +
        '"HpdHelpdesk".OWNERGROUP,' +
        '"HpdHelpdesk".COMPANY,' +
        '"HpdHelpdesk".CATEGORIZATIONTIER1,' +
        '"HpdHelpdesk".CATEGORIZATIONTIER2,' +
        '"HpdHelpdesk".CATEGORIZATIONTIER3,' +
        '"HpdHelpdesk".RESOLUTIONCATEGORY,' +
        '"HpdHelpdesk".RESOLUTIONCATEGORYTIER2,' +
        '"HpdHelpdesk".RESOLUTIONCATEGORYTIER3,' +
        '"HpdHelpdesk".REPORTEDSOURCE,' +
        '"HpdHelpdesk".DESCRIPTION,' +
        '"HpdHelpdesk".ID' +
' FROM "HpdHelpdesk" ' +
'WHERE "HpdHelpdesk".REPORTEDDATE > TO_DATE(' + last_reported_date + ',' + 'YYYY-MM-DD HH24:MI:SS)';

When I run this, I get the following Oracle error: Error: ORA-00907: missing right parenthesis

What is the proper way to format such a query to avoid this error?

MT0 · Accepted Answer · 2016-05-09 22:54:39Z

2

You are only selecting from a single table so you do not need to prefix every column name with the table name. Also, you are missing the quotes around the last reported date and format mask for the TO_DATE function at the end of the query.

var last_reported_date = '2016-05-09 18:16:59';

var query = 'SELECT INCIDENTNUMBER,' +
        'SUBMITTER,' +
        'REPORTEDDATE,' +
        'LASTRESOLVEDDATE,' +
        'OWNERGROUP,' +
        'COMPANY,' +
        'CATEGORIZATIONTIER1,' +
        'CATEGORIZATIONTIER2,' +
        'CATEGORIZATIONTIER3,' +
        'RESOLUTIONCATEGORY,' +
        'RESOLUTIONCATEGORYTIER2,' +
        'RESOLUTIONCATEGORYTIER3,' +
        'REPORTEDSOURCE,' +
        'DESCRIPTION,' +
        'ID' +
' FROM "HpdHelpdesk" ' +
'WHERE REPORTEDDATE > TO_DATE( \'' + last_reported_date + '\', \'YYYY-MM-DD HH24:MI:SS\')';

edited May 9, 2016 at 22:54

answered May 9, 2016 at 22:49

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Comments

Dan McGhan · Accepted Answer · 2016-05-10 01:45:02Z

Adding to MTO's answer, you'll need to add white space for each line break so you don't get one long string without any white space:

var last_reported_date = '2016-05-09 18:16:59';

var query = '' + 
    'SELECT INCIDENTNUMBER, ' +
        'SUBMITTER, ' +
        'REPORTEDDATE, ' +
        'LASTRESOLVEDDATE, ' +
        'OWNERGROUP, ' +
        'COMPANY, ' +
        'CATEGORIZATIONTIER1, ' +
        'CATEGORIZATIONTIER2, ' +
        'CATEGORIZATIONTIER3, ' +
        'RESOLUTIONCATEGORY, ' +
        'RESOLUTIONCATEGORYTIER2, ' +
        'RESOLUTIONCATEGORYTIER3, ' +
        'REPORTEDSOURCE, ' +
        'DESCRIPTION, ' +
        'ID ' +
    'FROM "HpdHelpdesk" ' +
    'WHERE REPORTEDDATE > TO_DATE( \'' + last_reported_date + '\', \'YYYY-MM-DD HH24:MI:SS\')';

Collectives™ on Stack Overflow

how to properly quote an Oracle SQL string in JavaScript

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related