node-oracledb error when executing a stored procedure

Question

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?

Answer 1

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.

Answer 2

A work-around is, if it has not been opened before then open the OUT_MENU_NAME cursor with zero rows in the exception blocks:

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

    -- Replicate your normal cursor output and filter on something that will never be true:
    OPEN  OUT_MENU_NAME FOR
      SELECT 'Nothing'         AS col1,
             DATE '1970-01-01' AS col2,
             0                 AS col3
      FROM DUAL
      WHERE 1 = 0;

  WHEN OTHERS THEN
    OUT_STATUS:='FAILURE';
    OUT_STATUS_DESC:=dbms_utility.format_error_backtrace;

    -- Replicate your normal cursor output and filter on something that will never be true:
    OPEN  OUT_MENU_NAME FOR
      SELECT 'Nothing'         AS col1,
             DATE '1970-01-01' AS col2,
             0                 AS col3
      FROM DUAL
      WHERE 1 = 0;