Node-oracledb: NJS-006: invalid type for parameter 1

[ahummel25]

I'm trying to execute a PL/SQL block that ends up calling a stored procedure. My code is identical to the solution identified here.

PLSQL Block:

const plSQL = 
`DECLARE

TYPE varchar2_table_100 IS TABLE OF VARCHAR2(100)
  INDEX BY PLS_INTEGER;
TYPE number_table IS TABLE OF NUMBER
  INDEX BY PLS_INTEGER;

l_po_number_array       varchar2_table_100;
l_po_line_number_array  number_table;
l_asn_number_array      varchar2_table_100;
l_asn_line_number_array number_table;
l_serial_number_array   varchar2_table_100;

l_rec my_pkg.my_pkg_rectype;
l_tab my_pkg.my_pkg_tabype;

BEGIN

l_po_number_array       := :poNumberArray;
l_po_line_number_array  := :poLineNumberArray;
l_asn_number_array      := :asnNumberArray;
l_asn_line_number_array := :asnLineNumberArray;
l_serial_number_array   := :serialNumberArray;

-- Now that the decomposed array values have made it over to Oracle, we
-- can put them back together as an array of record types.
FOR i IN 1 .. l_po_number_array.COUNT LOOP

  l_rec.po_number       := l_po_number_array(i);
  l_rec.po_line_number  := l_po_line_number_array(i);
  l_rec.asn_number      := l_asn_number_array(i);
  l_rec.asn_line_number := l_asn_line_number_array(i);
  l_rec.serial_number   := l_serial_number_array(i);

  l_tab(i) := l_rec;

END LOOP;

my_pkg.my_proc(p_tab => l_tab);

END;`;

module.exports = {
  plSQL
}

And here is the javascript to make the call..

'use strict'
/* eslint no-console: 0 */

const oracledb = require('oracledb') /* eslint-disable-line no-unused-vars */
const plSQL= require('../utils/plSQL')

module.exports = function (oracleDBService, utils) {
  return {
    async uploadSerials(payload) {
      const conn = await oracleDBService.getConnection()
      const data = await utils.getWorkSheetData(payload.file)
      const keys = Object.keys(data[0])
      let spreadSheetData = []
      let poNumberArray = []
      let poLineNumberArray = []
      let asnNumberArray = []
      let asnLineNumberArray = []
      let serialNumberArray = []

     //This is data from a spreadsheet.
      for (let result in data) {
        spreadSheetData[result] = {
          poNumber: data[result][keys[0]],
          poLineNumber: data[result][keys[1]],
          asnNumber: data[result][keys[2]],
          asnLineNumber: data[result][keys[3]],
          serialNumber: data[result][keys[4]]
        }
      }

      //All these arrays get populated correctly with matching lengths
      for (let i = 0; i < spreadSheetData.length; i++) {
        poNumberArray.push(spreadSheetData[i].poNumber)
        poLineNumberArray.push(spreadSheetData[i].poLineNumber)
        asnNumberArray.push(spreadSheetData[i].asnNumber)
        asnLineNumberArray.push(spreadSheetData[i].asnLineNumber)
        serialNumberArray.push(spreadSheetData[i].serialNumber)
      }

      const bind = {
        poNumberArray: {
          type: oracledb.STRING,
          dir: oracledb.BIND_IN,
          val: poNumberArray
        },
        poLineNumberArray: {
          type: oracledb.NUMBER,
          dir: oracledb.BIND_IN,
          val: poLineNumberArray
        },
        asnNumberArray: {
          type: oracledb.STRING,
          dir: oracledb.BIND_IN,
          val: asnNumberArray
        },
        asnLineNumberArray: {
          type: oracledb.NUMBER,
          dir: oracledb.BIND_IN,
          val: asnLineNumberArray
        },
        serialNumberArray: {
          type: oracledb.STRING,
          dir: oracledb.BIND_IN,
          val: serialNumberArray
        }
      }
      try {
        await conn.execute(plSQL, bind, (err, result) => {
          if (err) {
            console.log(err)
          }
        })
        return 'Success'
      } catch(err) {
        throw new Error(err)
      } finally {
        oracleDBService.releaseConnection(conn)
      }
    }
  }
}

I cannot figure out why I keep getting that NJS-006: invalid type for parameter 1 error. Please advise if you see any issues.

[cjbj]

Pro-tips to make it faster for people to help you:

• if the problem just one bind, then cut down the testcase to use just one bind
• provide a _runnable_ testcase
• answer the issue template questions

:)

[dmcghan]

@ahummel25 I agree with @cjbj, but after a quick glance at the code, I think you need to pass plSQL.plSQL, not just plSQL.

After you fix that, you'll need to remove the callback function you're passing to execute if you want it to work with await. Promises are only returned if you don't pass a callback function.

[ahummel25]

@dmcghan Just saw your comment over on the other site. How are you assigning the entire binded array to the PL/SQL type at one time?

[cjbj]

@dmcghan you are so kind to users :) Or have too much free time!

@ahummel25 (and others): if the eventual use of the data is to simply to insert it into the DB, I'd recommend using executeMany() since this will remove the need to hop between PL/SQL and SQL.

[ahummel25]

@dmcghan If you're curious, this is my solution to resolve the array assignment on the PL/SQL side. Couldn't get it to work outside of doing this.

I'll build a string of array(index) := values and then concatenate that on to my larger PL/SQL block that I pasted above.

for (let i = 0; i < spreadSheetData.length; i++) {
    block += `l_po_number_array(${i + 1}) := '${spreadSheetData[i].poNumber}';
               l_po_line_number_array(${i + 1}) := ${spreadSheetData[i].poLineNumber};
               l_asn_number_array(${i + 1}) := '${spreadSheetData[i].asnNumber}';
               l_asn_line_number_array(${i + 1}) := ${spreadSheetData[i].asnLineNumber};
               l_serial_number_array(${i + 1}) := '${spreadSheetData[i].serialNumber}';\n`
  }

[dmcghan]

@ahummel25 When I can't get something to work, I usually start by isolating the thing that isn't working. If it's something small, I start with this file:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

async function runTest() {
  let conn;

  try {
    conn = await oracledb.getConnection(config);

    let result = await conn.execute(
      'select * from dual'
    );

    console.log(result);
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

runTest();

In your case, you're testing array binds... So here's how I'd modify the file:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

async function runTest() {
  let conn;

  try {
    myArrayOfNumbers = [1, 2, 3];

    conn = await oracledb.getConnection(config);

    let result = await conn.execute(
     `declare
        type number_aat is table of number
          index by pls_integer;

        l_my_array_of_numbers number_aat;
      begin
        l_my_array_of_numbers := :bind_in;

        :bind_out := l_my_array_of_numbers.count;
      end;`,
      {
        bind_in: {
          val: myArrayOfNumbers
        },
        bind_out: {
          dir: oracledb.BIND_OUT,
          type: oracledb.NUMBER
        }
      }
    );

    console.log(result.outBinds);
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

runTest();

Does this run for you?

[cjbj]

Closing - no activity.