执行Node.js OracleDB查询遇NJS-098错误：绑定参数数量不匹配

Error: NJS-098: 16 positional bind values are required but 5 were provided

我已提供所有必需的绑定参数，但无法理解为何需要重复传递它们。官方文档明确说明，即使绑定参数在SQL中多次使用，也只需在绑定对象中出现一次。

版本信息 #

node version - 20.10.0
oracledb version - 6.3.0

相关代码 #

import { dashboardRecord, dashboardRecordsRequest, dashboardRecordsResponse } from "datamodel/dashboard/dashboard";
import { logger } from "logger/logger";
import * as OracleDB from "oracledb";

export async function getActiveRequestRecords(dashboardRecordsRequest: dashboardRecordsRequest): Promise<dashboardRecordsResponse> {
    const p_entityname: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.entityName, type: OracleDB.STRING };
    const p_sortby: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortBy, type: OracleDB.STRING };
    const p_sortOrder: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortOrder, type: OracleDB.STRING };
    const p_startindex: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.startIndex, type: OracleDB.NUMBER };
    const p_pagesize: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.pageSize, type: OracleDB.NUMBER };
    const p_totalrecords: OracleDB.BindParameter = { dir: OracleDB.BIND_OUT, type: OracleDB.NUMBER };
    const p_activerecords: OracleDB.BindParameter = { dir: OracleDB.BIND_OUT, type: OracleDB.CURSOR };
    const bindParams: OracleDB.BindParameter[] = [
        p_entityname,
        p_sortby,
        p_sortOrder,
        p_startindex,
        p_pagesize,
        p_totalrecords,
        p_activerecords
    ];
    const execOptions: OracleDB.ExecuteOptions = { outFormat: OracleDB.OUT_FORMAT_OBJECT, resultSet: true };

    const recordsSQL: string = `SELECT
                                    requestnumber,
                                    requeststatus,
                                    requestor,
                                    pendingwith,
                                    processtype,
                                    actiondate
                                FROM
                                    requests
                                WHERE
                                    ( ( upper(:p_entityname) = 'HR'
                                            AND processtype IN ( 'PROCTYPE1', 'PROCTYPE2' ) )
                                        OR ( upper(:p_entityname) = 'MANAGER'
                                            AND processtype NOT IN ( 'PROCTYPE1', 'PROCTYPE2' ) ) )
                                        AND AND upper(requeststatus) = 'PENDINGAPPROVAL'
                                ORDER BY
                                    ( CASE WHEN lower(:p_sortorder) = 'desc' AND lower(:p_sortby) = 'requestnumber' THEN requestnumber END ) DESC,
                                    ( CASE WHEN lower(:p_sortorder) = 'asc' AND lower(:p_sortby) = 'requestnumber' THEN requestnumber END ) ASC,
                                    ( CASE WHEN lower(:p_sortorder) = 'desc' AND lower(:p_sortby) = 'processtype' THEN processtype END ) DESC,    
                                    ( CASE WHEN lower(:p_sortorder) = 'asc' AND lower(:p_sortby) = 'processtype' THEN processtype END ) ASC,    
                                    ( CASE WHEN lower(:p_sortorder) = 'desc' AND lower(:p_sortby) =  'actiondate' THEN actiondate END ) DESC,
                                    ( CASE WHEN lower(:p_sortorder) = 'asc' AND lower(:p_sortby) =  'actiondate' THEN actiondate END ) ASC
                                OFFSET :p_startindex ROWS FETCH NEXT :p_pagesize ROWS ONLY`;

    try {
        const dbConnection = await OracleDB.getConnection('requestpool');
        const recordsResult = await dbConnection.execute(recordsSQL, bindParams, execOptions);
        const recordsResultSet = await recordsResult.resultSet?.getRows();
        console.log(recordsResultSet);
    } catch (err) {
        logger.error(err);
        console.error(err);
    }
}

解决方案 #

问题根源是绑定方式不匹配：

• SQL中使用的是命名绑定（:p_entityname、:p_sortby这类带名称的占位符），但代码里传递的是数组形式的位置绑定参数。
• node-oracledb会将数组参数识别为位置绑定，此时Oracle会把SQL里每个命名占位符都当作独立的位置参数，统计出16个需要绑定的位置，而你只传了5个入参，因此触发报错。

修复步骤： #

1. 将绑定参数从数组改为对象形式，键名对应SQL里的占位符名称：

const bindParams: OracleDB.BindParameters = {
    p_entityname,
    p_sortby,
    p_sortOrder,
    p_startindex,
    p_pagesize,
    p_totalrecords,
    p_activerecords
};

2. 修复SQL语法错误：WHERE子句里有连续两个AND（)) AND AND upper(requeststatus)），需改为单个AND。

修改后，命名绑定会正确识别重复的占位符，无需重复传递参数，同时SQL语法错误也会被修复，即可解决问题。

内容的提问来源于stack exchange，提问作者Ranjeet