Using a function to store cursors in oracle

Question

Hi so i created a stored procedure that contained many cursors and the cursor being used would be dependent on the variables being passed in. I had the idea of cleaning up my stored procedure by putting all the cursors in a function and then returning the cursor needed. That way if I needed to create anymore cursors, I can just modify the function.

here is my function:

create or replace function E_P_CURSOR (p_schoolid number, p_mode number, p_d varchar2) 
return sys_refcursor
is
rf_cur   sys_refcursor;
begin

IF (p_schoolid = 2243 and p_mode = 1) then
open rf_cur for
     SELECT ..........;

return rf_cur;
end if;

end E_P_CURSOR;

But when i try to compile my main procedure it gives an error.


Procedure P_DF(p_schoolid IN number, p_mode In Number)  As
 v_cursor      sys_refcursor;
....
....
BEGIN
v_cursor:= E_P_CURSOR (p_schoolid, p_mode, v_d);

FOR cur_rec IN v_cursor LOOP 
UTL_FILE.PUT_LINE(v_file,cur_rec.col1||cur_rec.col2||cur_rec.col3||cur_rec.col4||cur_rec.col5||cur_rec.col6||cur_rec.col7||cur_rec.col8);
END LOOP;  

END; 

..

error: [Error] PLS-00221 (671: 26): PLS-00221: 'V_CURSOR' is not a procedure or is undefined

....

have a look at https://stackoverflow.com/questions/42698175/pl-sql-for-loop-sys-refcursor — Shaun Peterson, Sep 25 '19 at 22:28

score 0 · Answer 1 · answered Sep 25 '19 at 19:10

0

You are missing a BEGIN

Procedure P_DF(p_schoolid IN number, p_mode In Number)  As
 v_cursor      sys_refcursor;
....
BEGIN  -- ADD THIS
v_cursor:= E_P_CURSOR (p_schoolid, p_mode, v_d);

FOR cur_rec IN v_cursor LOOP 
UTL_FILE.PUT_LINE(v_file,cur_rec.col1||cur_rec.col2||cur_rec.col3||cur_rec.col4||cur_rec.col5||cur_rec.col6||cur_rec.col7||cur_rec.col8);
END LOOP;   

..

answered Sep 25 '19 at 19:10

OldProgrammer

12,050
4
24
45

I actually do have a begin statement.. I just didnt paste it – johnjonfenomenon Sep 25 '19 at 20:08

Using a function to store cursors in oracle

1 Answers1