I'm trying to execute an SP in Express JS with "oracledb" dependency but when I try to get data from RS throws this error "Error: NJS-018: invalid ResultSet".
This is my SP i'm using Oracle 11G:
create or replace PROCEDURE SP_ASNRSAMERICA (asn_in IN ASN_TAB, p_cursor IN OUT SYS_REFCURSOR)
AS
mensaje_error VARCHAR2(200);
filas_insertadas INTEGER := 0;
BEGIN
BEGIN
FOR i IN 1..asn_in.COUNT LOOP
INSERT INTO
asntesttbl (order_number, line, purcharse_order, item_number, quantity,
price, total, adicharges, currency, reference,
harmcode, coo, location, invoice, invdate, exchange_rate)
VALUES
(asn_in(i).order_number, asn_in(i).line, asn_in(i).purcharse_order, asn_in(i).item_number, asn_in(i).quantity,
asn_in(i).price, asn_in(i).total, asn_in(i).adicharges, asn_in(i).currency, asn_in(i).reference,
asn_in(i).harmcode, asn_in(i).coo, asn_in(i).location, asn_in(i).invoice, TO_DATE(asn_in(i).invdate, 'YYYYMMDD'),
asn_in(i).exchange_rate);
filas_insertadas := filas_insertadas + 1;
END LOOP;
EXCEPTION
WHEN INVALID_NUMBER THEN
mensaje_error := 'Error asntesttbl: Valor no válido para la columna';
WHEN VALUE_ERROR THEN
mensaje_error := 'Error asntesttbl: Valor no compatible con el tipo de datos de la columna';
WHEN OTHERS THEN
mensaje_error := 'Error asntesttbl: Se ha producido un error desconocido al insertar los datos';
END;
IF filas_insertadas > 0 THEN
OPEN p_cursor FOR
SELECT 'SP EJECUTADO EXITOSAMENTE, NO. DE FILAS INSERTADAS: ' || filas_insertadas AS MESSAGE FROM DUAL;
ELSE
OPEN p_cursor FOR
SELECT mensaje_error AS MESSAGE FROM DUAL;
END IF;
END;
And this is my js function:
const postASNRSAmerica = async (body) => {
try {
const options = {
autoCommit: true,
prefetchRows: 1000, // tune the internal getRow() data fetch performance
fetchArraySize: 1000,
};
const result = await bd
.open(
`
BEGIN
SP_ASNRSAMERICA (:asn_in, :p_cursor );
END;`,
{
asn_in: {
type: "ASN_TAB",
val: body,
},
p_cursor: {
dir: oracledb.BIND_OUT,
type: oracledb.CURSOR,
},
},
options
)
.then((res) => {
console.log(res);
return res.outBinds.p_cursor;
})
.catch((err) => {
console.log(err);
console.log("Error al ejecutar el SP SP_ASNRSAMERICA", err);
});
console.log(result.metaData);
let row;
while ((row = await result.getRow())) {
console.log(row);
}
return {
status: 201,
message: result,
};
} catch (error) {
throw boom.badRequest("Catch: " + error);
}
};
When i try to get Rows is when throws the error and i made a log of the outBinds and this what i get:
{
"_rowCache": [],
"_processingStarted": false,
"_convertedToStream": false,
"_allowGetRowCall": false,
"_isActive": false,
"_parentObj": {
"_events": {},
"_eventsCount": 0,
"_dbObjectClasses": {},
"_requestQueue": [],
"_inProgress": true,
"_closing": true
}
}
I just want to get the result set of the SYS_REFCURSOR SP what do i have to change on the code Or the in the SP?
Simplify and debug. The following runs for me:
'use strict';
const oracledb = require('oracledb');
const dbConfig = require('./dbconfig.js');
let clientOpts = {};
if (process.platform === 'win32') { // Windows
clientOpts = { libDir: 'C:\\oracle\\instantclient_19_17' };
} else if (process.platform === 'darwin' && process.arch === 'x64') { // macOS Intel
clientOpts = { libDir: process.env.HOME + '/Downloads/instantclient_19_8' };
}
oracledb.initOracleClient(clientOpts);
async function run() {
let connection;
try {
connection = await oracledb.getConnection(dbConfig);
const plsql1 = `
create or replace PROCEDURE SP_ASNRSAMERICA (asn_in number, p_cursor IN OUT SYS_REFCURSOR)
AS
mensaje_error VARCHAR2(200);
filas_insertadas INTEGER := 0;
BEGIN
mensaje_error := 'Error asntesttbl: Valor no válido para la columna';
filas_insertadas := asn_in;
IF filas_insertadas > 0 THEN
OPEN p_cursor FOR
SELECT 'SP EJECUTADO EXITOSAMENTE, NO. DE FILAS INSERTADAS: ' || filas_insertadas AS MESSAGE FROM DUAL;
ELSE
OPEN p_cursor FOR
SELECT mensaje_error AS MESSAGE FROM DUAL;
END IF;
END;`;
await connection.execute(plsql1);
const plsql2 = `BEGIN
SP_ASNRSAMERICA (:asn_in, :p_cursor );
END;`;
const binds = {
asn_in: {
val: 123,
},
p_cursor: {
dir: oracledb.BIND_OUT,
type: oracledb.CURSOR,
},
};
const options = { outFormat: oracledb.OUT_FORMAT_OBJECT };
const res = await connection.execute(plsql2, binds, options);
const result = res.outBinds.p_cursor;
let row;
while ((row = await result.getRow())) {
console.log(row);
}
} catch (err) {
console.error(err);
} finally {
if (connection) {
try {
await connection.close();
} catch (err) {
console.error(err);
}
}
}
}
run();