hello i was trying to use refcursor that is returned from a function in a procedure here’s my function
create or replace
FUNCTION CUR_OUT
RETURN SYS_REFCURSOR
IS
l_rc sys_refcursor;
BEGIN
OPEN L_RC FOR
SELECT FIRST_NAME,SALARY
FROM EMPLOYEES;
RETURN L_RC;
end;
and here’s my procedure code that uses the refcursor
create or replace
PROCEDURE P_TEST (PAR_CURSOR IN SYS_REFCURSOR)
IS
-- PAR_CURSOR SYS_REFCURSOR;
L_ENAME VARCHAR2(20);
l_sal NUMBER(8,2);
BEGIN
loop
fetch par_cursor into l_ename, l_sal;
exit when par_cursor%notfound;
dbms_output.put_line(l_ename ||' - '|| l_sal);
end loop;
end;
and here’s the exection of the procedure
SET SERVEROUTPUT ON
DECLARE
BEGIN
P_TEST(CUR_OUT);
end;
but i am getting an error
"PL/SQL: Function returned without value"
*Cause: A call to PL/SQL function completed, but no RETURN statement was
executed.
*Action: Rewrite PL/SQL function, making sure that it always returns
a value of a proper type.
so what seems to be the problem here?
note when i use the code inside the function in the anonymous block directly and then call the procedure it works fine