Eugene Goldberg Eugene Goldberg - 6 months ago 12
Javascript Question

how to properly quote an Oracle SQL string in JavaScript

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 MT0
Answer

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\')';