oracle-databaseexpressstored-proceduresplsqlnode-oracledb

Get Result Set from Oracle stored procedure with cursor BIND OUT


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?


Solution

  • 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();