whenever i call this function it will return error like - invalid cursor

so, this is the body part of the package, the package specification also return a sys_refcursor. the error message shows that sys_refcursor invalid cursor.

FUNCTION SUMMARY
    (i_name VARCHAR2, i_id VARCHAR2, i_label VARCHAR2)
RETURN SYS_REFCURSOR
IS 
    rc_result           SYS_REFCURSOR;
    v_sql               CLOB;
    Server      VARCHAR2(100) := '@AI';
    v_r_count      NUMBER;
    v_sp_count       VARCHAR2(200);
    V VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'select count(*) from run'||Server||' where id='''||i_id||''' and label='''||i_label||''' and re is not null' INTO v_r_count;
    EXECUTE IMMEDIATE 'select table_name from all_tables where table_name like ''%se%'' and owner ='''||i_name||'''' INTO v_sp_count;
    
    IF v_r_count > 0 THEN 
    BEGIN
        v_sql := 'select RE from run'||v_Server||' where id='''||i_id||''' and label='''||i_label||'''';      
    END;   
    ELSIF v_sp_count IS NOT NULL THEN
    BEGIN
        v_sql := 'SELECT
                        process,
                        desc,
                        p_desc,
                        date
                    FROM
                        '||i_name||'.se
                    WHERE
                            errors = 1
                        AND lower(p_desc) LIKE lower(''%summary%'')';
    END;
    ELSE v_sql := NULL;
    END IF;
    
    OPEN result FOR v_sql;    
    RETURN result;
    
    EXCEPTION 
        WHEN no_data_found THEN
             RETURN NULL;
        WHEN OTHERS THEN
            dbms_output.put_line(SQLERRM);
END;

CodePudding user response：

You can simply assign NULL to the refcursor like this: Example

PROCEDURE test(  id_number IN VARCHAR2,
                      resultIN OUT SYS_REFCURSOR) AS
    BEGIN
      if false then
        OPEN resultIN FOR
          SELECT dummy 
          from dual;
      ELSE
         resultIN := null;
      END IF;
    END;