I have stored procedure and there are many out variables in it. So I am calling the stored procedure like this:

export const infoHR3 = async () => {
  try {
    const sql =
      ` 
      Declare
        ln_order_qty  NUMBER;
        ln_in_proc_qty_hr  NUMBER;
        ln_procd_hr_mass  NUMBER;
        ln_in_proc_qty  NUMBER;
        ln_wip  NUMBER;
        ln_qa  NUMBER;
        ln_packing  NUMBER;
        ln_dispatchable_qty  NUMBER;
        ln_despatched_qty  NUMBER;
        ln_finished_qty  NUMBER;
        ln_balance_qty  NUMBER;
        ln_bal_disp_qty  NUMBER;
      BEGIN
        CRMDBA.C1DVX007(
          '9514593782',
          '1',
          1,
          ln_order_qty,
          ln_in_proc_qty_hr,
          ln_procd_hr_mass,
          ln_in_proc_qty,
          ln_wip,
          ln_qa,
          ln_packing,
          ln_dispatchable_qty,
          ln_despatched_qty,
          ln_finished_qty,
          ln_balance_qty,
          ln_bal_disp_qty
        );
        dbms_output.put_line(ln_order_qty);
      END;  `;
    return await query(sql);
  } catch (error) {
    console.log(error);
    throw new Error.InternalServerError("Error!");
  }
};

This is the model:

function getinfoHR3Table() {
  return infoHR3();
}
    
export const ProcessModel = {
      getProcess,
      getReason,
      getinfoHR1Table,
      getinfoHR2Table,
      getinfoCR1Table,
      getinfoCR2Table,
      getinfoHR3Table
};

This is the controller:

export const getinfoHR3Table = async (req: Request, res: Response) => {
  try {
    const results: any = await ProcessModel.getinfoHR3Table();
    return res.status(200).json(results);
  } catch (error) {
    return res.status(400).json(error);
  }
};

The problem is I am getting blank value in the result. How do I extract out variables and return them when calling a stored procedure??

The problem with this is that DBMS_OUTPUT.PUT_LINE just prints line in the database. The procedure does not return any values which can be used in the backend. However it gives values to outvariables.

How can I extract these outvariables from the procedure so that I can use them in the backend?

CodePudding user response：

You can create a Stored Procedure with arguments of type IN OUT in order to use the parameters in a versatile manner such as

CREATE OR REPLACE PROCEDURE CALL_C1DVX007(
                                          id IN NUMBER,
                                          in_prm1 IN NUMBER,
                                          in_prm2 IN NUMBER,
                                          ln_order_qty IN OUT NUMBER,
                                          ln_in_proc_qty_hr IN OUT NUMBER,
                                          ln_procd_hr_mass IN OUT NUMBER,
                                          ln_in_proc_qty IN OUT NUMBER,
                                          ln_wip IN OUT NUMBER,
                                          ln_qa IN OUT NUMBER,
                                          ln_packing IN OUT NUMBER,
                                          ln_dispatchable_qty IN OUT NUMBER,
                                          ln_despatched_qty IN OUT NUMBER,
                                          ln_finished_qty IN OUT NUMBER,
                                          ln_balance_qty  IN OUT NUMBER,
                                          ln_bal_disp_qty IN OUT NUMBER
                                        ) 
BEGIN
  CRMDBA.C1DVX007(id,
                  in_prm1,
                  in_prm2,
                  ln_order_qty,
                  ln_in_proc_qty_hr,
                  ln_procd_hr_mass,                  
                  ln_in_proc_qty,
                  ln_wip,
                  ln_qa,
                  ln_packing,
                  ln_dispatchable_qty,
                  ln_despatched_qty,
                  ln_finished_qty,
                  ln_ba lance_qty,
                  ln_bal_disp_qty);
END;
/

and call

SQL> SET serveroutput ON
SQL> BEGIN 
      CALL_C1DVX007(9514593782,1,1,:ln_order_qty,:ln_in_proc_qty_hr....);
     END;
     /
SQL> DBMS_OUTPUT.PUT_LINE(:ln_order_qty); -- to display a result coming from a parameter

from the command line

CodePudding user response：

/If ln_order_qty is the output variable from the procedure, you can capture the value of this variable into another variable and use it from there onwards. Below is an example:-/

ln_order_qty_output := ln_order_qty;

/* The above variable ln_order_qty_output will now contain the output variable value from the procedure and you can use it as a value in the backend PLSQL procedure */

    Declare
      ln_order_qty  NUMBER;
      ln_in_proc_qty_hr  NUMBER;
      ln_procd_hr_mass  NUMBER;
      ln_in_proc_qty  NUMBER;
      ln_wip  NUMBER;
      ln_qa  NUMBER;
      ln_packing  NUMBER;
      ln_dispatchable_qty  NUMBER;
      ln_despatched_qty  NUMBER ;
      ln_finished_qty  NUMBER;
      ln_balance_qty  NUMBER;
      ln_bal_disp_qty  NUMBER;
 BEGIN
    

CRMDBA.C1DVX007('9514593782','1',1,ln_order_qty,ln_in_proc_qty_hr,ln_procd_hr_mass,

 ln_in_proc_qty,ln_wip,ln_qa,ln_packing,ln_dispatchable_qty,ln_despatched_qty,
 ln_finished_qty,ln_balance_qty,ln_bal_disp_qty);
 ln_order_qty_output := ln_order_qty;    
 dbms_output.put_line(ln_order_qty);    
 dbms_output.put_line(ln_order_qty_output);    
END;     
  

/*Both the values displayed on the dbms_output.put_line should be the same You can now use the value of the variable ln_order_qty_output in the backend */