CODEWITHSUNDEEP
sqloracle-databasecursor
user2989054
user2989054

Reputation: 1

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;
}

Upvotes: 0

Views: 518

Answers (1)

sulica
sulica

Reputation: 111

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;

Upvotes: 1

Related Questions