Q: How do I return a SYS_REFCURSOR from a stored procedure after a loop? I am a bit new to Oracle and PL SQL. I have a procedure like the following.
{
CREATE OR REPLACE PROCEDURE MyProcedure (MyRecordset OUT SYS_REFCURSOR)
AS
BEGIN 
    DECLARE

        CURSOR MyRecordset IS 
        select
        ...
        from table1, table2, etc..

    BEGIN
        FOR Record in MyRecordset
        LOOP        
            --Do something                             
        END LOOP; -- IMPLICIT CLOSE OCCURS
        -- THIS IS WHERE I NEED TO RETURN THE CURSOR. DOES THIS NOT OPEN IT AT BEGINNING AGAIN?
        OPEN MyRecordset;
    END;
END MyProcedure;
/
}

I need to return a SYS_REFCURSOR. Is this sufficient? When I try to test it with the following in Toad I get no output in the data grid.
{
DECLARE
 type result_set is ref cursor; 
BEGIN
 BIZTALK.GetCustomerPaymentsDebug(:result_set);
END;
}


A: This example works for me. I think it should help you.
 
Declare cursor like:
 TYPE genCurType IS REF CURSOR;

PROCEDURE return_in
 (  p_ime           IN     VARCHAR2
  , po_seznam       OUT    genCurType
  , po_errid        OUT    errorIdType
  , po_errmsg       OUT    errorMsgType
  )
IS
 cc_module_name CONSTANT  VARCHAR2(60):= 'return_ins';
BEGIN
        OPEN po_seznam   FOR SELECT IME, OPIS, NAZIV, OBRAZEC
                             FROM test
                             WHERE upper(IME) = upper(p_ime);
EXCEPTION
  WHEN OTHERS THEN
        po_errid := SQLCODE;
        po_errmsg := 'Poizvedba ni bila uspešna! ('||SQLERRM||''||gc_package||'.'||cc_module_name||')';

END;

