oracle: Error: NJS-012: encountered invalid bind data type in parameter 2

I am inserting new users into users table in Oracle db but encountering this error, oracle: Error: NJS-012: encountered invalid bind data type in parameter 2 and when i have looked at the parameter being indicated, i see no problem !

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));
        });
        }
});

Below is the output from console.log('sp: ' + JSON.stringify(sp)); Looking more closely at console output, no invalid data type is found.

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"}}
}

i am now encountering this error after execution, oracle: Error: NJS-012: encountered invalid bind data type in parameter 2.

Any help ?

Thanks in advance.

Solution

You shouldn't be using the DB_TYPE_* constants. From the doc:

These values indicate the Oracle Database type shown in extended metadata for queries and REF CURSORS, and also shown for DbObject types.

You can use the 3.1.2 Node-oracledb Type Constants: https://oracle.github.io/node-oracledb/doc/api.html#-312-node-oracledb-type-constants

Constants for execute() bind parameter type property, for the createLob() type parameter, for the Lob type property, for fetchAsBuffer, for fetchAsString and fetchInfo, and for extended metadata.

Keep in mind that you don't need to specify the type with "in" binds because they all have default mappings based on the JS type. https://oracle.github.io/node-oracledb/doc/api.html#-201-in-bind-parameters