Get Data from Oracle SP on Express

Question

I have an SP in Oracle which I return the data from a TYPE TABLE in a SYS_REFCURSOR but when the data arrives in the code all the properties arrive as NULL. This is my SP:

create or replace PROCEDURE SP2 (idCliente IN INTEGER, items_tab IN ITEM_TAB, pCursor OUT SYS_REFCURSOR)
AS
    idTipoCliente INTEGER;
    precio    NUMBER;
    idMoneda  NUMBER;
    dFecha  DATE;
    nFactor NUMBER;
    priceMxn NUMBER;
    priceUsd NUMBER;
    IdAlmacen INTEGER default 3;
    IdContrato INTEGER default 0;
    idCencosto INTEGER default 0;
    idPrecioDif INTEGER default 0;
    itemsPrice RESULT_TAB := RESULT_TAB();
BEGIN
   -- Inicializando la tabla de resultados

    -- Obtener idTipoCliente
    SELECT ID_TIPOCTEPREC INTO idTipoCliente 
        FROM CLIENTE 
        WHERE ID_CLIENTE = idCliente;
    
    SELECT DFECHA, NFACTOR 
                INTO dFecha, nFactor
                FROM tipocambio 
                WHERE ROWNUM<2 ORDER BY id_tipocambio DESC;
                
    FOR i IN 1..items_tab.COUNT LOOP
        IF f_MTP_ConsultaPrecio(items_tab(i).itemId, idCliente, idTipoCliente, IdAlmacen, IdContrato, precio, idMoneda, idCencosto, idPrecioDif) THEN
            -- Calculando los precios
            priceMxn := CASE WHEN idMoneda = 1 THEN precio ELSE precio * nFactor END;
            priceUsd := CASE WHEN idMoneda = 2 THEN precio ELSE precio / nFactor END;
            
            --DBMS_OUTPUT.PUT_LINE('Item ID: ' || items_tab(i).itemId || ', Precio: ' || TO_CHAR(precio) || ', ID Moneda: ' || TO_CHAR(idMoneda) || ', Fecha: ' || TO_CHAR(dFecha) || ', Factor: ' || TO_CHAR(nFactor) || ', Precio MXN: ' || TO_CHAR(priceMxn) || ', Precio USD: ' || TO_CHAR(priceUsd));
            -- Añadiendo el resultado a la tabla de resultados
            itemsPrice.EXTEND;
            itemsPrice(i) := RESULT_OBJ(
                items_tab(i).itemId,
                precio,
                idMoneda,
                dFecha,
                nFactor,
                priceMxn,
                priceUsd
            );
            
        ELSE
        DBMS_OUTPUT.PUT_LINE('wdadw');
            itemsPrice.EXTEND;
            itemsPrice(i) := RESULT_OBJ(
                items_tab(i).itemId,
                NULL, -- precio
                NULL, -- id_moneda
                NULL, -- dFecha
                NULL, -- nFactor
                NULL, -- priceMxn
                NULL  -- priceUsd
            );
        END IF;
    END LOOP;
    
    OPEN pCursor FOR
        SELECT * FROM TABLE(itemsPrice);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        idTipoCliente := 0;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END SP2;

This are my TYPES

create or replace TYPE ITEMS_OBJ AS OBJECT (
   itemId NUMBER
);

create or replace TYPE ITEM_TAB AS TABLE OF ITEMS_OBJ;


create or replace TYPE RESULT_OBJ AS OBJECT (
   ITEMID   NUMBER(9, 0),
   PRECIO    NUMBER(13,4),
   ID_MONEDA NUMBER(9, 0),
   DFECHA    DATE,
   NFACTOR   NUMBER(13,4),
   PRICEMXN  NUMBER(13,4),
   PRICEUSD  NUMBER(13,4)
);

create or replace TYPE RESULT_TAB AS TABLE OF RESULT_OBJ;

And when i execute my SP on Oracle this is the output:

Item ID: 123494, Precio: 289.59, Moneda: 1, Fecha: 26-SEP-2023, Factor: 17.3733, Precio MXN: 289.59, Precio USD: 16.6687
Item ID: 5991, Precio: 205.3711, Moneda: 2, Fecha: 26-SEP-2023, Factor: 17.3733, Precio MXN: 3567.9744, Precio USD: 205.3711
Item ID: 129782, Precio: 60.613, Moneda: 2, Fecha: 26-SEP-2023, Factor: 17.3733, Precio MXN: 1053.0471, Precio USD: 60.613
Item ID: 96157, Precio: 45.143, Moneda: 1, Fecha: 26-SEP-2023, Factor: 17.3733, Precio MXN: 45.143, Precio USD: 2.5984
Item ID: 114161, Precio: 26.045, Moneda: 1, Fecha: 26-SEP-2023, Factor: 17.3733, Precio MXN: 26.045, Precio USD: 1.4991

But when i execute this SP on node I get all data as NULL like this:

{ITEMID: null, PRECIO: null, ID_MONEDA: null, DFECHA: null, NFACTOR: null, …}

This is my function:

async function getOrangeItemPrice(data) {
  let connection;

  try {
    connection = await oracledb.getConnection(dbConfig.oracle);

    const plsql = `BEGIN
                      SP2 (:idCliente, :items_tab, :pCursor);
                   END;`;

    const binds = {
      idCliente: {
        type: oracledb.NUMBER,
        val: data.clientId,
      },
      items_tab: {
        type: "ITEM_TAB",
        val: data.items,
      },
      pCursor: {
        dir: oracledb.BIND_OUT,
        type: oracledb.CURSOR,
      },
    };

    const options = { autoCommit: true, outFormat: oracledb.OUT_FORMAT_OBJECT };

    const spRS = await connection.execute(plsql, binds, options);

    const spResult = spRS.outBinds.pCursor;

    let rowData = [];
    while ((row = await spResult.getRow())) {
      let resp = {
        DFECHA: row.DFECHA,
        ID_MONEDA: row.ID_MONEDA,
        ITEMID: row.ITEMID,
      };

      rowData.push(resp);
    }

    await connection.close();

    return rowData;
  } catch (error) {
    throw boom.badRequest("Catch: " + error);
  }
}

What do I have to change on my SP to get all the data?

I test changing this part to check data fech and works:

OPEN pCursor FOR
        SELECT 'TEST' as outType FROM DUAL;

But with the TYPE TABLE SELECT don't work

Answer 1

Some suggestions of possible solutions and methods of debugging:

1. You can simplify ITEM_TAB to be:

   CREATE TYPE ITEM_TAB AS TABLE OF NUMBER;
   

   and drop the ITEMS_OBJ object as unnecessary. In the procedure, you would then use items_tab(i) instead of items_tab(i).itemId.

   Using a simple table of numbers may be much easier in Node than passing a table of objects.

2. In Oracle, if you call the procedure as:

   DECLARE
     v_cur SYS_REFCURSOR;
   BEGIN
     SP2 (1, ITEM_TAB(ITEMS_OBJ(1), ITEMS_OBJ(2),ITEMS_OBJ(3)), v_cur);
   END;
   /
   

   or if you have dropped ITEMS_OBJ and used a table of numbers:

   DECLARE
     v_cur SYS_REFCURSOR;
   BEGIN
     SP2 (1, ITEM_TAB(1, 2, 3), v_cur);
   END;
   /
   

   Then in Node, try to call the procedure with static values defined in the PL/SQL block and see if that works:

   const plsql = `BEGIN
                    SP2 (:idCliente, ITEM_TAB(1, 2, 3), :pCursor);
                  END;`;
   
   const binds = {
     idCliente: {
       type: oracledb.NUMBER,
       val: data.clientId,
     },
     pCursor: {
       dir: oracledb.BIND_OUT,
       type: oracledb.CURSOR,
     },
   };
   

   If it does work then you know the problem is with the bind of the collection and can look at how to solve that one problem.

   If it does not work then you can simplify even more until you can workout where the issue is.

3. You call f_MTP_ConsultaPrecio and if the function returns FALSE then you output NULL values. Are you sure that your procedure is not performing properly and, instead, it is this function that is failing?