Node.js调用Oracle 21c存储过程返回空数组，参数传递失败

环境 #

• Oracle 21c
• Node.js v20

问题描述 #

调用存储过程时客户端参数无法传递至服务端，但使用硬编码参数可正常返回结果。通过Postman携带查询参数测试，数据库中存在对应记录，但接口返回空数组。

相关代码 #

const oracledb = require('oracledb');

const mypw = ... // the hr schema password

async function getRecords(req, res) {
    let connection;

    // client inputs
    let p1 = req.query.p1;
    let p2 = req.query.p2;

    try {
        connection = await oracledb.getConnection({
            user: "myuser",
            password: mypw,
            connectString: "localhost/DB1"
        });

        const result = await connection.execute(
            `BEGIN myproc(:p1, :p2, :p_cursor); END;`,
            { // bind variables
                p1: { dir: oracledb.BIND_IN, type: oracledb.STRING },
                p2: { dir: oracledb.BIND_IN, type: oracledb.STRING },
                p_cursor: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR },
            },
            [],
        );
        console.log(result.rows);
        res.send(result.rows); // <-- empty array

    } catch (err) { // catches errors in getConnection and the query
        res.send(err);
    } finally {
        if (connection) {   // the connection assignment worked, must release
            try {
                await connection.release();
            } catch (e) {
                console.error(e);
            }
        }
    };
}

解决方案 #

问题出在绑定变量的定义上：你只指定了参数的方向和类型，但没有把客户端获取到的p1、p2实际值传递给绑定变量，导致存储过程接收到的是空参数，自然返回空结果。

另外原代码直接访问result.rows的方式错误，游标类型的输出参数需要单独处理才能获取数据。修正后的代码如下：

const oracledb = require('oracledb');

const mypw = ... // the hr schema password

async function getRecords(req, res) {
    let connection;

    // client inputs
    let p1 = req.query.p1;
    let p2 = req.query.p2;

    try {
        connection = await oracledb.getConnection({
            user: "myuser",
            password: mypw,
            connectString: "localhost/DB1"
        });

        const result = await connection.execute(
            `BEGIN myproc(:p1, :p2, :p_cursor); END;`,
            { // bind variables - 修正：传入实际参数值
                p1: { dir: oracledb.BIND_IN, type: oracledb.STRING, val: p1 },
                p2: { dir: oracledb.BIND_IN, type: oracledb.STRING, val: p2 },
                p_cursor: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR },
            },
            [],
        );
        // 处理游标获取数据
        const cursor = result.outBinds.p_cursor;
        const rows = await cursor.getRows();
        await cursor.close();
        
        console.log(rows);
        res.send(rows);

    } catch (err) { // catches errors in getConnection and the query
        res.send(err);
    } finally {
        if (connection) {   // the connection assignment worked, must release
            try {
                await connection.release();
            } catch (e) {
                console.error(e);
            }
        }
    };
}

如果不需要显式指定参数类型，也可以用更简洁的绑定写法（oracledb会自动推断类型和默认的IN方向）：

{
    p1: p1,
    p2: p2,
    p_cursor: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR }
}

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