oracle / oracle/node-oracledb

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

Open
#1,595 10 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

enhancement workaround available
Dominant language
JavaScript
Stars
2.4k
Forks
1.1k
PR merge metrics
No merged PRs in 30d

Description

  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!

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

  2. 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
    )`;

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the supplied connection.executeMany call, the PL/SQL block, and the direct INSERT comparison. Reproduce the bind error using the listed values and compare how executeMany handles the procedure invocation versus the INSERT; done means identifying a confirmed cause and documenting or testing the corrected behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, node.js, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.