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

[mostafa-gado]

1. What versions are you using?
   6.0.3

Give your database version.
Oracle Cloud ATP 21c

process.platform:
'win32'
process.version:
'v18.12.1'
process.arch:
'x64'
require('oracledb').versionString:
'6.0.3'
require('oracledb').oracleClientVersionString:
Uncaught:
Error: NJS-089: getting the Oracle Client version is not supported by node-oracledb in Thin mode
at throwErr (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\errors.js:592:10)
at Object.throwNotImplemented (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\errors.js:601:3)
at get oracleClientVersionString [as oracleClientVersionString] (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\oracledb.js:1064:14) {
code: 'NJS-089'
}

But I am running in Thick mode!

2. Is it an error or a hang or a crash?
   Error

3. What error(s) or behavior you are seeing?
   Error: NJS-012: encountered invalid bind data type in parameter 2

I am trying to use executeMany to run a procedure in the database but I am getting this error "Error: NJS-012: encountered invalid bind data type in parameter 2".

The database proc:

PROCEDURE CR_CONTRACT_ITEM(
    p_sale_cont_no IN NUMBER,
    p_item_no IN NUMBER,
    p_qty IN NUMBER,
    p_item_price IN NUMBER,
    p_disc_percentage IN NUMBER,
    p_disc_amount IN NUMBER,
    p_price_after_disc IN NUMBER,
    p_total_price IN NUMBER,
    p_comments IN VARCHAR2,
    p_rec_user IN NUMBER,
    p_upd_user IN NUMBER,
    R_STATE OUT VARCHAR2
    )
IS
    
BEGIN
       
INSERT INTO sale_contract_items (
    sale_cont_no,
    item_no,
    qty,
    item_price,
    disc_percentage,
    disc_amount,
    price_after_disc,
    total_price,
    comments,
    rec_user,
    upd_user,
    rec_date,
    upd_date
) VALUES (
    p_sale_cont_no,
    p_item_no,
    p_qty,
    p_item_price,
    p_disc_percentage,
    p_disc_amount,
    p_price_after_disc,
    p_total_price,
    p_comments,
    p_rec_user,
    p_upd_user,
    sysdate,
    sysdate
);

    R_STATE := 0;
     
EXCEPTION
        WHEN OTHERS THEN
           R_STATE := SUBSTR( DBMS_UTILITY.format_error_stack|| DBMS_UTILITY.format_error_backtrace, 1, 4000);
            RAISE;
END;

The node.js code:

const contItemsSql = `BEGIN 
     UTL_SALES.CR_CONTRACT_ITEM(
      :sale_cont_no,
      :item_no,
      :qty,
      :item_price,
      :disc_percentage,
      :disc_amount,
      :price_after_disc,
      :total_price,
      :comments,
      :rec_user,
      :upd_user
     );
     END;`;

const dummyItems = [
        {
          sale_cont_no: 10274,
          item_no: 328,
          qty: "1",
          item_price: 120,
          disc_percentage: 0,
          disc_amount: 0,
          price_after_disc: 120,
          total_price: 120,
          comments: null,
          rec_user: 42,
          upd_user: 42,
          R_STATE: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 200 },
        },
      ];
 const contItemsResult = await connection.executeMany(
        contItemsSql,
        itemsWithContId
      );

The strange thing is If I ran the same executeMany with the same parameters but instead of calling the proc I call the same insert statement, It works. The same insert statement that is inside the proc.

The insert statement as requested by @sudarshan12s :

const contItemsSql = `INSERT INTO sale_contract_items(
      sale_cont_no,
      item_no,
      qty,
      item_price,
      disc_percentage,
      disc_amount,
      price_after_disc,
      total_price,
      rec_user,
      upd_user,
      comments
     ) VALUES (
      :sale_cont_no,
      :item_no,
      :qty,
      :item_price,
      :disc_percentage,
      :disc_amount,
      :price_after_disc,
      :total_price,
      :rec_user,
      :upd_user,
      :comments
    )`;

[sharadraju]

Thanks for reporting the issue. We are looking into it.

Note that the default behavior after node-oracledb 6.0 is the Thin mode.
So unless you have called initOracleClient(), the program will invoke the Thin mode of node-oracledb.

[sharadraju]

@mostafa-gado The parameter 2 qty: "1" in your bind variables array dummyItems is assigned a string instead of a NUMBER.
Please change this to qty: 1 as qty is a NUMBER.

Please specify the exact INSERT statement that you used, which ran correctly. Does it have the same bind values?

Did you run the INSERT statement in a Node.js program or as a separate SQL?

[sudarshan12s]

@mostafa-gado since you have binds with input and output, Can you provide bindDefs something like this.

const dummyItems = [
  {
    sale_cont_no: 10274,
    item_no: 328,
    qty: 1,
    item_price: 10274,
    disc_percentage: 0,
    disc_amount: 0,
    price_after_disc: 12,
    total_price: 12,
    comments: "xyz",
    rec_user: 42,
    upd_user: 42
  },
];

const options = {
  bindDefs: {
    sale_cont_no: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    item_no: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    qty: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    item_price: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    disc_percentage: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    disc_amount: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    price_after_disc: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    total_price: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    comments: { dir: oracledb.BIND_IN, type: oracledb.STRING, maxSize: 20},
    rec_user: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    upd_user: { dir: oracledb.BIND_IN, type: oracledb.NUMBER},
    R_STATE: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 200 }
  }
};
result = await connection.executeMany(
      contItemsSql,
      dummyItems,
      options
    );

[mostafa-gado]

@sharadraju Yes I called initOracleClient()

[mostafa-gado]

@sudarshan12s Changing the qty to int doesn't solve the issue. I updated the post with the insert statement.

[mostafa-gado]

@sudarshan12s Using bindDefs raised a different error:

Error: Error: NJS-011: encountered bind value and type mismatch
    at Object.throwErr (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\errors.js:592:10)
    at checkType (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\transformer.js:71:16)
    at Object.transformValueIn (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\transformer.js:245:3)
    at Connection._processBindValue (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\connection.js:303:37)
    at Connection._processExecuteManyBinds (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\connection.js:454:20)
    at async Connection.executeMany (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\connection.js:936:15)
    at async Connection.<anonymous> (D:\Apps\ERP\etit-erp-backend\node_modules\oracledb\lib\util.js:162:14)
    at async createContractDb (D:\Apps\ERP\etit-erp-backend\db\sales\contractDb.js:313:31)
    at async createContract (D:\Apps\ERP\etit-erp-backend\controllers\sales\contractController.js:60:18) {
  code: 'NJS-011'
}

[sudarshan12s]

Can you give me the complete program or the executeMany arguments you passed. I passed same as in my comment, it works .

Above error can come if the bind values doesn't match the type mentioned in bindDefs like if we pass string but its type is defined as oracledb.NUMBER
qty: '1',

I modified your program to something like this which works. similar examples are in test folder

const myProc = `CREATE OR REPLACE PROCEDURE CR_CONTRACT_ITEM(
    p_sale_cont_no IN NUMBER,
    p_item_no IN NUMBER,
    p_qty IN NUMBER,
    p_item_price IN NUMBER,
    p_disc_percentage IN NUMBER,
    p_disc_amount IN NUMBER,
    p_price_after_disc IN NUMBER,
    p_total_price IN NUMBER,
    p_comments IN VARCHAR2,
    p_rec_user IN NUMBER,
    p_upd_user IN NUMBER,
    R_STATE OUT VARCHAR2
    )
AS
    
BEGIN
R_STATE := 'OUTVAL';
END;`;


const contItemsSql = `BEGIN 
     CR_CONTRACT_ITEM(
      :sale_cont_no,
      :item_no,
      :qty,
      :item_price,
      :disc_percentage,
      :disc_amount,
      :price_after_disc,
      :total_price,
      :comments,
      :rec_user,
      :upd_user,
      :R_STATE
     );
     END;`;

const dummyItems = [
  {
    sale_cont_no: 10274,
    item_no: 328,
    qty: 1,
    item_price: 10274,
    disc_percentage: 0,
    disc_amount: 0,
    price_after_disc: 12,
    total_price: 12,
    comments: "xyz",
    rec_user: 42,
    upd_user: 42
  },
];

const options = {
  bindDefs: {
    sale_cont_no: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    item_no: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    qty: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    item_price: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    disc_percentage: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    disc_amount: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    price_after_disc: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    total_price: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    comments: { dir: oracledb.BIND_IN, type: oracledb.STRING, maxSize: 20 },
    rec_user: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    upd_user: { dir: oracledb.BIND_IN, type: oracledb.NUMBER },
    R_STATE: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 200 }
  }
};

  //oracledb.initOracleClient(clientOpts);  // enable node-oracledb Thick mode

async function run() {
  let connection;

  try {
    
    connection = await oracledb.getConnection(dbConfig);
    let result = await connection.execute(myProc);
    result = await connection.executeMany(
      contItemsSql,
      dummyItems,
      options
    );
    console.log("Query outbinds :", result.outBinds);

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        // Connections should always be released when not needed
            await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();

[mostafa-gado]

@sudarshan12s Ok I found the issue. After carefully examining your code I noticed you are only adding the R_STATE definition in the bindDefs. But I was adding the R_STATE definition twice. Once in the dummyItems and again in the bindDefs. Once I removed it from the dummyItems It worked.
Thanks!

[mostafa-gado]

I think I found some inconsistency between execute() and executeMany(). It seems that in executeMany() you can't include the out params definition in the parameters passed to the function, And you must include them in options.bindDefs.
But in execute() you must include the out params in the params passed and you don't have to include them in options.bindDefs.
Don't you think this is confusing?

[sharadraju]

I am changing this as an enhancement as this is a behavior change request:
execute does not have bindDefs as part of the options parameter to define bind variables. It defines the properties of the bind variables (val, dir, type) as part of the binds parameter
executeMany requires bindDefs as part of the options parameter to define the properties of the bind variables (dir, type)