Search code examples
node.jsoraclenode-oracledb

node-oracledb error when executing a stored procedure

I'm using Node 10.x,and Oracledb 3.0

I'm trying to execute a stored procedure in node.js

I've Exception block in the procedure,like below:

EXCEPTION
WHEN NO_DATA_FOUND THEN
OUT_STATUS:='FAILURE';
OUT_STATUS_DESC:='USER NOT MAPPED TO A GROUP';
WHEN OTHERS THEN
OUT_STATUS:='FAILURE';
OUT_STATUS_DESC:=dbms_utility.format_error_backtrace;

whenever control goes to Exception block throwing below error in node.js.

Error: ORA-24338: statement handle not executed

But,explicitly if i execute procedure in db with same IN parameters it's giving proper OUT parameters with error and CURSOR is closed.

These are the OUT parameter types in procedure.

OUT_STATUS OUT VARCHAR2, OUT_STATUS_DESC OUT VARCHAR2, OUT_MENU_NAME OUT SYS_REFCURSOR

What could be the issue?

Solution

  • All OUT variables must be set to something even if you don't intend to use them in the node-oracledb code, see https://github.com/oracle/node-oracledb/issues/886

    It turns out this is true in all Oracle DB drivers (e.g. node-oracledb) based on Oracle's C 'OCI' API, but symptomatic problems are more commonly seen in node-oracledb.