如何解决NodeJS调用Oracle存储过程时的NJS-097绑定占位符错误

在NodeJS环境中调用Oracle存储过程时触发NJS-097错误，提示找不到名为:P_TOTALRECORDS的绑定占位符，但该存储过程在SQL Developer中可正常执行并返回预期结果。

NodeJS代码 #

const router = express.Router();

router.get('/activerequests', async (req, res, next) => {

    const entityName: string = req.body.entityName;
    const sortBy: string = req.body.sortBy;
    const sortOrder: string = req.body.sortOrder;
    const startIndex = req.body.startIndex | 0;
    const pageSize = req.body.pageSize | 50;
    const conn = createMyApvDBConnection(await requestdbconnpool);
    const sql = `BEGIN 
                    get_dashboard_active_request_records(:p_entityname, :p_sortby, :p_sortorder, :p_startindex, :p_pagesize, p_totalrecords, p_activerecords);
                END;`;

    try {
        const dbresult = await (await conn)?.execute(sql, {
            p_entityname: { dir: OracleDB.BIND_IN, val: entityName, type: OracleDB.STRING },
            p_sortby: { dir: OracleDB.BIND_IN, val: sortBy, type: OracleDB.STRING },
            p_sortOrder: { dir: OracleDB.BIND_IN, val: sortOrder, type: OracleDB.STRING },
            p_startindex: { dir: OracleDB.BIND_IN, val: startIndex, type: OracleDB.NUMBER },
            p_pagesize: { dir: OracleDB.BIND_IN, val: pageSize, type: OracleDB.NUMBER },
            p_totalrecords: { dir: OracleDB.BIND_OUT, type: OracleDB.NUMBER },
            p_activerecords: { dir: OracleDB.BIND_OUT, type: OracleDB.CURSOR }
        });
        res.send(dbresult?.outBinds);
    } catch (err) {
        logger.error(err);
        res.status(500).send(`Error while retrieving Active Request records. Please contact the Administrator.`);
    } finally {
        if (conn) {
            try {
                await (await conn)?.close();
            } catch (err) {
                logger.error('Error while closing database connection', err);
            }
        }
    }
});

Oracle存储过程代码 #

create or replace PROCEDURE get_dashboard_active_request_records (
    p_entityname     IN   VARCHAR2,
    p_sortby         IN   VARCHAR2,
    p_sortorder      IN   VARCHAR2,
    p_startindex     IN   NUMBER,
    p_pagesize       IN   NUMBER,
    p_totalrecords   OUT  NUMBER,
    p_activerecords  OUT  SYS_REFCURSOR
) AS
BEGIN
    
dbms_output.put_line( 'Entity Name - ' || p_entityname || ', Sort By - ' || p_sortby || ', Sort Order - ' || p_sortorder || ', Start Index - ' || p_startindex || ', Page Size - ' || p_pagesize);

v_reqstatus := 'PENDINGAPPROVAL';
SELECT 
    COUNT(*)INTO p_totalrecords
FROM
    requests
WHERE
    upper(requeststatus) = v_reqstatus
        AND ( ( upper(p_entityname) = 'ENT1' AND processtype IN ( 'PROC1', 'PROC2' ) )
            OR ( upper(p_entityname) = 'ENT2' AND processtype NOT IN ( 'PROC1', 'PROC2' ) ) );

dbms_output.put_line( 'Entity Name - ' || p_entityname || ', Total Active Requests: ' || p_totalrecords);

OPEN p_activerecords FOR 
SELECT
      requestnumber,
      requeststatus,
      requestor,
      pendingwith,
      processtype,
      actiondate
FROM
      requests
WHERE
      upper(requeststatus) = v_reqstatus
          AND ( ( upper(p_entityname) = 'ENT1' AND processtype IN ( 'PROC1', 'PROC2' ) )
              OR ( upper(p_entityname) = 'ENT2' AND processtype NOT IN ( 'PROC1', 'PROC2' ) ) )
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;

END get_dashboard_active_request_records;

错误信息 #

{"level":"ERROR","timestamp":"2024-03-12T08:47:34.258Z","pid":16136,"host":"localhost","node_version":"v21.6.1","err":{"type":"Error","message":"NJS-097: no bind placeholder named ":P_TOTALRECORDS" was found in the statement text","stack":"Error: NJS-097: no bind placeholder named ":P_TOTALRECORDS" was found in the statement text\n at Object.throwErr (C:\code\tutorials\myappserver\node_modules\oracledb\lib\errors.js:603:10)\n at ThinConnectionImpl._bind (C:\code\tutorials\myappserver\node_modules\oracledb\lib\thin\connection.js:847:16)\n at ThinConnectionImpl._execute (C:\code\tutorials\myappserver\node_modules\oracledb\lib\thin\connection.js:176:18)\n at processTicksAndRejections (node:internal/process/task_queues:95:5)\n at async ThinConnectionImpl.execute (C:\code\tutorials\myappserver\node_modules\oracledb\lib\thin\connection.js:930:14)\n at async Connection.execute (C:\code\tutorials\myappserver\node_modules\oracledb\lib\connection.js:861:16)\n at async Connection. (C:\code\tutorials\myappserver\node_modules\oracledb\lib\util.js:165:14)\n at async C:\code\tutorials\myappserver\src\routes\dashboard\getactiverequestrecords.ts:21:26","code":"NJS-097"},"msg":"NJS-097: no bind placeholder named ":P_TOTALRECORDS" was found in the statement text"}

问题原因与修复方案 #

问题出在调用存储过程的SQL语句中：输出参数p_totalrecords和p_activerecords未添加冒号作为绑定占位符，导致oracledb驱动无法识别这些绑定项。

将原SQL语句修改为：

BEGIN 
    get_dashboard_active_request_records(:p_entityname, :p_sortby, :p_sortorder, :p_startindex, :p_pagesize, :p_totalrecords, :p_activerecords);
END;

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