Problem with pass params to stored procedure in oracledb node.js module-CodePudding

I am creating project project on Node.js and using oracledb module. I should invoke a stored procedure with parameters, however when I try to pass number parameter to the procedure, I get this error:

(node:6952) UnhandledPromiseRejectionWarning: Error: ORA-06502: PL/SQL: numeric or value error

This is the procedure:

create or replace procedure delete_song
(s_id in number,  procedure_result out boolean) is
begin
delete from PLAYLIST_SONGS where SONG_ID = s_id;
delete  from SONG where ID = s_id;
procedure_result:=true;
commit ;
exception when others
    then
procedure_result:= false;
rollback;
end;

My code in Node.js app, where I execute procedure:

router.delete("/:id", async (req, res) => {
  const connection = await orcldb.getConnection(dbconf);

  let in_id;

  if (req.params.id === undefined) {
    throw new Error("Bad request");
  } else {
    in_id = parseInt(req.params.id);
  }

  console.info("id: ", in_id, typeof in_id);

  let procedureResult = await connection.execute(
    `
    BEGIN 
      DB_ADMIN.DELETE_SONG(:id, :ret);
     END;`,
    {
      id: in_id,
      ret: { dir: orcldb.BIND_OUT, type: orcldb.DB_TYPE_BOOLEAN },
    }
  );

  let result = procedureResult.outBinds.ret;

  if (!result) {
    throw new Error("Delete song failed");
  }

  resultSet.close();
  res.end("Success");
});

When I invoked this procedure from datagrip (for example) all works correctly, script:

declare
    result boolean;
begin
    DB_ADMIN.DELETE_SONG(41, result);
end;

Connection to db and get data work correctly. And my db schema:

Database schema

Db user can invoke stored procedures. I am using Oracle 12c.

I hope anybody help me, and I sorry for my bad English.

CodePudding user response：

Seems fine for me with 19c. Is the problem with the IN or the OUT bind? I know there was one issue with BOOLEAN binding when mixing little endian and big endian for the client & server. This was fixed in 12.2.

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

if (process.platform === 'darwin') {
  oracledb.initOracleClient({libDir: process.env.HOME   '/Downloads/instantclient_19_8'});
}

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(dbConfig);

    await connection.execute(
      `create or replace procedure delete_song
          (s_id in number, procedure_result out boolean) is
       begin
          procedure_result:=true;
       end;`);

    let in_id = 1;
    let procedureResult = await connection.execute(
      `BEGIN DELETE_SONG(:id, :ret); END;`,
      { id: in_id,
        ret: { dir: oracledb.BIND_OUT, type: oracledb.DB_TYPE_BOOLEAN },
      });

    let result = procedureResult.outBinds.ret;
    console.log(result);

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();

The output is:

$ node t.js
true

Your validation should check whether parseInt() returns NaN. If you try to bind this for in_id you will get DPI-1055: value is not a number (NaN) and cannot be used in Oracle numbers