Stored Procedure result is not showing in Datagrid pane in Toad for Oracle

oracletoad

I have created a procedure in Toad for Oracle 12. When I execute the procedure it does not show the dataset in datagrid pane, while the procedure is being executed successfully. What I am doing wrong?

CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(OpNo CTS_OPERATIONS.OPERATIONNUM%Type ,BName CTS_OPERATIONS.BLENDNAME%Type) 
AS
p_recordset Types.cursor_type;
BEGIN
    OPEN p_recordset FOR

    SELECT FILLED_MCB.BARCODE,TILTED_MCB.TILTINGTIME,MAKERS.FEEDERNUM,MAKERS.MAKERNUM 
    FROM FILLED_MCB INNER JOIN TILTED_MCB ON FILLED_MCB.FM_ID = TILTED_MCB.FM_ID 
    INNER JOIN MAKERS ON MAKERS.M_ID = TILTED_MCB.M_ID 
    INNER JOIN CTS_OPERATIONS ON CTS_OPERATIONS.CTS_ID = FILLED_MCB.CTS_ID

    --WHERE CTS_OPERATIONS.OPERATIONNUM=OpNo AND CTS_OPERATIONS.BLENDNAME=BName;
    WHERE CTS_OPERATIONS.OPERATIONNUM=3004 AND CTS_OPERATIONS.BLENDNAME='PG';
END;

—- Execute Procedure —————

exec FEEDERS_TO_MAKERS_MCB(3004,'PG');

enter image description here

Best Answer

I think you want an output parameter of the type sys_refcursor. Currently your cursor is assigned to a local variable, which gets destroyed when the procedure terminates.

CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(
   OpNo CTS_OPERATIONS.OPERATIONNUM%Type 
   ,BName CTS_OPERATIONS.BLENDNAME%Type

   ,p_recordset OUT SYS_REFCURSOR

) 
AS
BEGIN
    OPEN p_recordset FOR

    SELECT ...
END;
Related Question