Node.js执行PL/SQL块遇ORA-01036错误，请求排查原因

问题场景 #

你编写的PL/SQL块在Oracle SQL Developer中运行正常，但通过Node.js调用时触发ORA-01036: illegal variable name/number错误。

你的PL/SQL代码 #

DECLARE
    user_name varchar(255);
    custom_exception EXCEPTION;
    PRAGMA exception_init( custom_exception, -20001);
BEGIN
    SELECT name 
    INTO user_name
    FROM data_table where ID = '1';
    
    IF user_name  = 'temp' THEN 
        RAISE custom_exception;
    END IF;
END;

你的Node.js调用代码 #

var output = await connection.execute(fs.readFileSync('path_to_pl_sql_file', 'utf8'), {
    message: {
        dir: oracledb.BIND_OUT,
        type: oracledb.STRING,
        maxSize: 100
    }
}, {
    autoCommit: true
});

问题根源 #

Node.js代码里指定了一个名为message的输出绑定变量，但你的PL/SQL块中完全没有定义这个变量，Oracle数据库找不到对应的绑定项，因此抛出非法变量名/编号的错误。

解决方法 #

根据你的需求选择以下两种方案之一：

方案1：不需要输出变量，直接移除绑定参数 #

如果你的PL/SQL块不需要返回任何输出值，直接删除execute方法的第二个参数（绑定变量对象）即可：

var output = await connection.execute(fs.readFileSync('path_to_pl_sql_file', 'utf8'),
    {}, // 空绑定参数对象，也可直接省略该参数
    {
        autoCommit: true
    }
);

方案2：需要输出变量，修改PL/SQL块添加对应绑定项 #

如果你确实需要返回输出信息（比如异常提示），需要在PL/SQL块中声明对应的OUT绑定变量，并在逻辑中赋值。示例如下：

修改后的PL/SQL块：

DECLARE
    user_name varchar(255);
    custom_exception EXCEPTION;
    PRAGMA exception_init( custom_exception, -20001);
BEGIN
    SELECT name 
    INTO user_name
    FROM data_table where ID = '1';
    
    IF user_name  = 'temp' THEN 
        RAISE custom_exception;
    END IF;
    -- 正常情况赋值输出信息
    :message := '操作成功';
EXCEPTION
    WHEN custom_exception THEN
        -- 异常情况赋值错误信息
        :message := '用户名为temp，触发自定义异常';
    WHEN OTHERS THEN
        :message := '未知错误: ' || SQLERRM;
END;

对应的Node.js调用代码可保持不变，执行后通过output.outBinds.message即可获取输出内容。

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