Returning a cursor , calling a procedure from other procedure

Question

I have 2 procedures in same package. I wish to use QOT_LST_M_QOT_PRE in QOT_LST_M_SEC_EXC_PRE. In QOT_LST_M_SEC_EXC_PRE I wish to find the argument - x_qot_id, call QOT_LST_M_QOT_PRE with this argument and also return it instead of the statement. Can I do it? How. I mean something like

    PROCEDURE QOT_LST_M_SEC_EXC_PRE (
        i_sec_id     IN NUMBER,
        i_exc_id     IN NUMBER,
        o_recordset  OUT SYS_REFCURSOR ) IS x_qot_id NUMBER(10); 
        ------------------------------   
    BEGIN

    ---------------------------------------------------------------

    --call a function instead of writing query from this function
    open o_recordset for QOT_LST_M_QOT_PRE(x_qot_id, o_recordset);
    ----------------------------------------------------------------

    END  QOT_LST_M_SEC_EXC_PRE;





     PROCEDURE QOT_LST_M_QOT_PRE
    (
        i_qot_id     IN NUMBER,            
        o_recordset  OUT SYS_REFCURSOR
        --------------------------------
    );

Answer 1

Sure you can. You can declare out parameters of type SYS_REFCURSOR and use them in your procedures, here is an example:

CREATE OR REPLACE PROCEDURE QOT_LST_M_QOT_PRE (i_qot_id IN NUMBER, THE_CURSOR OUT SYS_REFCURSOR) --Declare a sys_refcursor to be an out parameter, so it can be used outside
AS
BEGIN
 OPEN THE_CURSOR FOR SELECT * FROM THE_TABLE WHERE X=i_qot_id;--Open the cursor
END;

CREATE OR REPLACE PROCEDURE QOT_LST_M_SEC_EXC_PRE (i_sec_id IN NUMBER, i_exc_id IN NUMBER)
AS
x_qot_id NUMBER(10); --Test param
RESULT_CURSOR SYS_REFCURSOR;--The cursor that will hold the opened cursor in QOT_LST_M_QOT_PRE procedure
SOMEVARIABLE VARCHAR2(10);--Test variables to hold results of cursor
BEGIN
 QOT_LST_M_QOT_PRE(1,RESULT_CURSOR);--Procedure will open RESULT_CURSOR
 LOOP --Loop cursor
    FETCH RESULT_CURSOR INTO SOMEVARIABLE;
    EXIT WHEN RESULT_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('READ :'||SOMEVARIABLE);
 END LOOP;
 CLOSE RESULT_CURSOR;--Close the opened cursor
END;