Check Values in sys_refcursor

Question

I have the following code in a function

        CREATE OR REPLACE FUNCTION my_func (
        v_dt events.raised_date%TYPE
        )
        RETURN SYS_REFCURSOR
    IS    
            p_events    SYS_REFCURSOR;

        OPEN p_events FOR
                SELECT  event_id
                  FROM  events
                 WHERE  raised_date = v_dt;
RETURN p_events;
END;

I would like to check whether 100 exists in p_events cursor or not. How can I do this inside my function.

Any help is highly appreciable.

Answer 1

It is not good idea to check it inside of the function. You are missing why the cursor is returned. Instead do it outside of the function.

DECLARE
    l_rc SYS_REFCURSOR := my_func();

    TYPE events_ntt IS TABLE OF NUMBER;
    l_events  events_ntt;

    l_lookup  events_ntt := events_ntt(100);
    l_diff    events_ntt;
BEGIN
    FETCH l_rc BULK COLLECT INTO l_events;

    l_diff := l_events MULTISET INTERSECT DISTINCT l_lookup;

    IF l_diff.COUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE('100 EXISTS');
    ELSE
        DBMS_OUTPUT.PUT_LINE('100 DOES NOT EXIST');
    END IF;
END;

Using Cursor Variables (REF CURSORs)

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. A cursor variable is more flexible because it is not tied to a specific query. You can open a cursor variable for any query that returns the right set of columns.

You pass a cursor variable as a parameter to local and stored subprograms. Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval. This technique is also useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language, such as Java or Visual Basic.

What Are Cursor Variables (REF CURSORs)?

Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs.

Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa.

Source: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i7106

(Oracle Database PL/SQL User's Guide and Reference)