Code Pl/sql when call function returning a sys refcursor

Question

I will gratefully if someone can help me with this function in oracle pl/sql. I trying to return a cursor with ids from one table, but when try to execute the Anonyme block calling the function I receive an exception with code ORA-06504. The functión returns a cursor when select get registers from query, but when the query not get registers I need to raise the personalize exception. Thanks a lot for your help...

            create or replace function FN_VALIDA_DISPONIBILIDAD(f_desde in date , f_hasta in date)return SYS_REFCURSOR 
            is 
                v_prop  propiedad%rowtype;
                c_prop_disponibles sys_refcursor;
                fecha_no_disponible exception;
                manso_error exception;
                aux number:=0;
            begin  
            
                    OPEN c_prop_disponibles FOR     
                    select p4.id_propiedad
                    from propiedad p4
                    where p4.id_propiedad NOT IN
                    (
                        SELECT p.ID_PROPIEDAD
                        FROM propiedad p
                        LEFT JOIN reserva r
                        ON r.id_propiedad = p.ID_PROPIEDAD
                        WHERE r.ID_RESERVA IS  NULL
                        AND ( 
                        (f_desde > r.FECHA_TERMINO_RESER)
                        OR
                        (f_desde < r.FECHA_INICIO_RESER
                        AND
                        f_hasta< r.FECHA_INICIO_RESER )
                        )
                        OR
                        (r.FECHA_INICIO_RESER BETWEEN f_desde AND f_hasta)
                        OR 
                        r.FECHA_TERMINO_RESER BETWEEN f_desde AND f_hasta
                        )
                     ORDER BY ID_PROPIEDAD;     
                    LOOP
                    FETCH c_prop_disponibles INTO v_prop;
                    EXIT WHEN c_prop_disponibles%NOTFOUND;
                         if c_prop_disponibles%notfound then
                          DBMS_OUTPUT.PUT_LINE('No hay');
                          aux:=0;              
                          elsif c_prop_disponibles%found then
                           DBMS_OUTPUT.PUT_LINE('hay');
                           --return c_prop_disponibles; 
                            aux:=1;
                           else
                           raise manso_error;
                          end if;
                    END LOOP;
                    begin 
                        if aux = 1 then
                        return c_prop_disponibles; 
                        else
                        raise fecha_no_disponible;
                        end if;            
                    end;
                    
                    
                   exception
                when fecha_no_disponible then
                   raise_application_error(-20006, 'No hay disponibilidad!!! '||sqlcode||' - '||'ERROR '||SQLERRM); 
                when manso_error then 
                    raise_application_error(-20017,'Ocurrio un manso error!!!'||sqlcode||' - '||'ERROR '||SQLERRM);     
                when others then 
                    raise_application_error(-20011,'Ocurrio un error!!!'||sqlcode||' - '||'ERROR '||SQLERRM);      
                
            end FN_VALIDA_DISPONIBILIDAD;

Answer 1

Lets recapitulate your question:

How to return a cursor from a function if the cursor returns data but raise an exception if the cursor is empty.

So first re-think if this a good approach and the empty test can't be done by the client calling the function.

If you want to perform the test in the function you must fetch at least one row from the cursor and test the NOTFOUND function.

The subtle problem is atht after this you can't simple return the cursor in the original state and you must close it and reopen it again.

Simple implementation would be

create  or replace function FN_VALIDA(f_par in varchar2) return SYS_REFCURSOR  as
 l_cur sys_refcursor;
 l_dummy VARCHAR(100); 
  no_maso_error exception;
begin
  open l_cur for
  select dummy from dual where dummy = f_par;
  -- test if the cursor is empt
  fetch l_cur into l_dummy;
  if l_cur%NOTFOUND then
     raise no_maso_error;
  else
    close l_cur;
    open l_cur for
    select dummy from dual where dummy = f_par;    
    return l_cur;  
  end if;
exception
     when no_maso_error then
       raise_application_error(-20006, 'Ocurrio no maso error!!! '||sqlcode||' - '||'ERROR '||SQLERRM);   
end;
/

See also more details in the similar question