REF_CURSOR with cast collect #1464
Description
- What versions are you using?
oracledb: 5.3.0
node: 14.16.0
- Is it an error or a hang or a crash?
crash
- What error(s) or behavior you are seeing?
Program stop working;
- 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.
