oracle: Error: NJS-012:在参数2中遇到无效的绑定数据类型

Question

我正在将新用户插入到users表中的Oracle db中，但是遇到了这个错误，oracle: Error: NJS-012: encountered invalid bind data type in parameter 2，当我查看所指示的参数时，我没有发现问题！

function storedProcs() {
this.SP_USER_ADD_USER = {
    name: 'sp_user_adduser',
    params: {
        username    :  {val: null, type: dbParams.STRING, dir : dbParams.BIND_IN},
        password    :  {val: null, type: dbParams.STRING, dir : dbParams.BIND_IN},
        salt        :  {val: null, type: dbParams.STRING, dir : dbParams.BIND_IN},
        userTypeId  :  {val: null, type: dbParams.NUMBER, dir : dbParams.BIND_IN},
        isActive    :  {val: null, type: dbParams.NUMBER, dir : dbParams.BIND_IN},
        isTeller    :  {val: null, type: dbParams.NUMBER, dir : dbParams.BIND_IN},
        userLoginIpAuthTypeId       :  {val: null, type: dbParams.NUMBER, dir : dbParams.BIND_IN},
        userLoginIp :  {val: null, type: dbParams.STRING, dir : dbParams.BIND_IN},
        forceChangePassword         :  {val: null, type: dbParams.STRING, dir : dbParams.BIND_IN},
        customerId  :  {val: null, type: dbParams.NUMBER, dir : dbParams.BIND_IN},
        userId      :  {val: null, type: dbParams.NUMBER, dir : dbParams.BIND_OUT},
        errorMsg    :  {val: null, type: dbParams.STRING, dir : dbParams.BIND_OUT}
    }
    }
    }

   oracleDbAssist.getConnection(function(err,conn){
        if(err){
            console.log('error: - getConnection ' + JSON.stringify(err));
            return;
        }

        for(var i = 0; i < myData.length; i++) {
            session.newUser = myData[i];
            var sp = new StoredProcs().SP_USER_ADD_USER;
            sp.params.username.val = session.newUser.userName;
            sp.params.salt.val = util.generateSalt();
            sp.params.password.val = util.hash(session.newUser.password, sp.params.salt.val);
            sp.params.userTypeId.val = session.newUser.userTypeId;
            sp.params.isActive.val = session.newUser.isActive;
            sp.params.isTeller.val = session.newUser.isTeller;
            sp.params.forceChangePassword.val = session.newUser.forceChangePassword == 0 ? '0' : '1';
            sp.params.customerId.val = session.newUser.customerId;
            sp.params.userLoginIpAuthTypeId.val = session.newUser.userLoginIpAuthTypeId;
            sp.params.userLoginIp.val = session.newUser.userLoginIp;
            console.log('sp: ' + JSON.stringify(sp));
        oracleDbAssist.executeSqlWithConn(sp,true,conn, function(err,result){
            if(err){
                console.log('error:- executeSqlWithConn ' + err);
                return;
            }

            console.log('New user added: ' + JSON.stringify(result));
        });
        }
});

下面是console.log('sp: ' + JSON.stringify(sp));更仔细地查看控制台输出的输出，没有找到无效的数据类型。

sp: {
"name":"sp_user_adduser",
"params":{
"username":{"val":"200216","type":"STRING","dir":"BIND_IN"},
"password":{"val":"e64b580d65b34219826a1a89fc85dc8e17b97f59","type":"STRING","dir":"BIND_IN"},
"salt":{"val":"895909","type":"STRING","dir":"BIND_IN"},
"userTypeId":{"val":3,"type":"NUMBER","dir":"BIND_IN"},
"isActive":{"val":1,"type":"NUMBER","dir":"BIND_IN"},
"isTeller":{"val":0,"type":"NUMBER","dir":"BIND_IN"},
"userLoginIpAuthTypeId":{"val":1,"type":"NUMBER","dir":"BIND_IN"},
"userLoginIp":{"val":null,"type":"STRING","dir":"BIND_IN"},
"forceChangePassword":{"val":"1","type":"STRING","dir":"BIND_IN"},
"customerId":{"val":null,"type":"NUMBER","dir":"BIND_IN"},
"userId":{"val":null,"type":"NUMBER","dir":"BIND_OUT"},
"errorMsg":{"val":null,"type":"STRING","dir":"BIND_OUT"}}
}

现在我在执行之后遇到了这个错误，oracle: Error: NJS-012: encountered invalid bind data type in parameter 2。

有什么帮助吗？

提前谢谢。

Answer 1

您不应该使用DB_TYPE_*常量。从医生那里：

这些值指示用于查询和引用游标的扩展元数据中显示的类型，以及DbObject类型。

您可以使用3.1.2节点-oracledb常量：https://oracle.github.io/node-oracledb/doc/api.html#-312-node-oracledb-type-constants

execute()绑定参数类型属性、createLob()类型参数、Lob类型属性、fetchAsBuffer、fetchAsString和fetchInfo以及扩展元数据的常量。

请记住，您不需要指定带有" in“绑定的类型，因为它们都具有基于JS类型的默认映射。https://oracle.github.io/node-oracledb/doc/api.html#-201-in-bind-parameters