REF_CURSOR with cast collect

[yakov-rs]

1. What versions are you using?

oracledb: 5.3.0 node: 14.16.0

2. Is it an error or a hang or a crash?

crash

3. What error(s) or behavior you are seeing?

Program stop working;

4. Include a runnable Node.js script that shows the problem.

index.js

const oracledb = require('oracledb');

async function executePLSQL() {
    let connection;

    try {
        connection = await oracledb.getConnection({
            user: "YOUR_SCHEMA",
            password: 'YOUR_PASSWORD',
            connectString: "YOUR_CONNECTION_STRING"
        });

        let result = await connection.execute(`begin getDataCursor1(p_cur => :p_cur); end;`,
            {
                p_cur: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
            });

        let resultSet = result.outBinds.p_cur;

        console.log(await resultSet.getRows());

        await resultSet.close();

        result = await connection.execute(`begin getDataCursor2(p_cur => :p_cur); end;`,
            {
                p_cur: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
            });

        resultSet = result.outBinds.p_cur;

        console.log(await resultSet.getRows());

        await resultSet.close();

        result = await connection.execute(`begin getDataCursor3(p_cur1 => :p_cur1, p_cur2 => :p_cur2); end;`,
            {
                p_cur1: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT},
                p_cur2: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
            });

        resultSet = result.outBinds.p_cur1;
        console.log(await resultSet.getRows());
        await resultSet.close();

        console.log('ok', 1);
        resultSet = result.outBinds.p_cur2;
        console.log('ok', 2);
        /*
          next block stop executing whole program
          no errors
          doesn't work both "finally"
        */
        try {
            let rows = await resultSet.getRows();
            console.log(rows);
        } catch (e) {
            console.log(e);
        } finally {
            await resultSet.close();
        }

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

}

executePLSQL();

In database

create or replace procedure getDataCursor1(
    p_cur out sys_refcursor
  ) 
is
begin
  open p_cur for
  select
    level
  from
    dual
  connect by level < 10;
end
/

create or replace procedure getDataCursor2(
    p_cur out sys_refcursor
  ) 
is
begin
  open p_cur for
  select
    group_by,
    cast(collect(lvl) as sys.odcinumberlist) group_values
  from
    (
      select
        mod(level, 3) group_by,
        level lvl
      from
        dual
      connect by level < 10
    )
  group by 
    group_by;
end
/

create or replace procedure getDataCursor3(
    p_cur1 out sys_refcursor,
    p_cur2 out sys_refcursor
  ) 
is
begin
  getDataCursor1(p_cur1);
  getDataCursor2(p_cur2);
end
/

Please help me to understand what is the problem. If you need some more information feel free to ask me.

[cjbj]

I can reproduce it. @pvenkatraman will take a look

[yakov-rs]

If it's matter, i'm using Oracle 18

[yakov-rs]

@cjbj , @pvenkatraman please tell me when it will be fixed?

[anthony-tuininga]

I'll let Chris comment on when it will be officially fixed. For now you can patch the code yourself using the following diff:

diff --git a/src/njsVariable.c b/src/njsVariable.c
index c0b78585..c87ba338 100644
--- a/src/njsVariable.c
+++ b/src/njsVariable.c
@@ -921,12 +921,15 @@ bool njsVariable_processJS(njsVariable *vars, uint32_t numVars, napi_env env,
         if (var->bindDir != NJS_BIND_OUT &&
                 var->varTypeNum != DPI_ORACLE_TYPE_STMT)
             continue;
-        if (!var->dmlReturningBuffers)
-            return njsVariable_processBufferJS(var, var->buffer, env, baton);
-        for (j = 0; j < var->numDmlReturningBuffers; j++) {
-            buffer = &var->dmlReturningBuffers[j];
-            if (!njsVariable_processBufferJS(var, buffer, env, baton))
+        if (!var->dmlReturningBuffers) {
+            if (!njsVariable_processBufferJS(var, var->buffer, env, baton))
                 return false;
+        } else {
+            for (j = 0; j < var->numDmlReturningBuffers; j++) {
+                buffer = &var->dmlReturningBuffers[j];
+                if (!njsVariable_processBufferJS(var, buffer, env, baton))
+                    return false;
+            }
         }
     }

[yakov-rs]

@anthony-tuininga Thanks. It's worked. I hope that your changes will be pushed to repository. It's inconvenient to copy and compile files into several platforms and dockers by myself (against "npm install" command).

[anthony-tuininga]

Glad to hear that it worked for you! These changes will make it to the repository. @cjbj will let you know when but it should be "soon". :-)

[cjbj]

@yakov-rs I merged the changes to the main branch, so you could simplify your build and use our code, see https://oracle.github.io/node-oracledb/INSTALL.html#githubtags

We will wrap up the 5.4 release soon.

[cjbj]

This was fixed back in 5.4.0. Thanks for reporting it.