I have the folowing code. There's outer procedure having inner_procedure for processing cursors (concatenating data from cursor to m (message) variable). I can open cursor and send reference to inner procedure for processing like:
PROCEDURE outer_proc AS
m VARCHAR2(2000):='';
cur SYS_REFCURSOR;
PROCEDURE inner_proc(cur IN SYS_REFCURSOR,m OUT VARCHAR2) IS
firstname VARCHAR2(20);
lastname VARCHAR2(20);
BEGIN
LOOP
FETCH cur INTO firstname,lastname;
EXIT WHEN cur%NOTFOUND;
m:=m||firstname||' '||lastname;
END LOOP;
END;
BEGIN
OPEN cur FOR SELECT * FROM employees WHERE sallary<1000;
inner_proc(cur,m);
CLOSE cur;
OPEN cur FOR SELECT * FROM employees WHERE sallary>=1000;
inner_proc(cur,m);
CLOSE cur;
END;
But I'd like to send explicit cursor name to inner procedure and leave inner procedure to open cursor and process it like:
PROCEDURE outer_proc AS
TYPE cur_type IS REF CURSOR;
m VARCHAR2(2000):='';
CURSOR c1 IS SELECT * FROM employees WHERE sallary<1000;
CURSOR c2 IS SELECT * FROM employees WHERE sallary>=1000;
PROCEDURE inner_proc(cur IN cur_type,m OUT VARCHAR2) IS
col1 VARCHAR2(20);
col2 VARCHAR2(20);
BEGIN
OPEN cur;
LOOP
FETCH cur INTO col1,col2;
EXIT WHEN cur%NOTFOUND;
m:=m||col1||' '||col2;
END LOOP;
CLOSE cur;
END;
BEGIN
inner_proc(c1,m);
inner_proc(c2,m);
END;
In my example above cursors accidentally do have the same %ROWTYPE but inner_procedure cannot know that in advance. My inner procedure should receive as parameter an arbitrary cursor, open it and finally concatenate first and second cursor's columns to message.
In my first code using SYS_REFCURSOR in parameter I might send reference to different cursors as well, so... why I have to send reference of already opened cursor, I'd like that process of opening and closing cursor be the part of inner procedure. I'd like to send name of cursor and leave opening and closing (as traversing through, of course) to inner procedure.
How can I achieve this?
From the documentation on explicit cursors (emphasis added):
You cannot assign a value to an explicit cursor, use it in an expression, or use it as a formal subprogram parameter or host variable. You can do those things with a cursor variable (see "Cursor Variables").
So you cannot send an explicit cursor, c1 or c2 in your example - you can't have a procedure that takes an explicit cursor argument.
You are allowed to supply a ref cursor as a formal parameter, as you already doing. That could be an open cursor, as you have; or a cursor variable that you then open. But you don't really seem to want the cursor outside the procedure call, so neither seems quite what you want.
If you had a small-ish defined list of cursors you could literally pass the name of the cursor as a string, and then use a case statement in the procedure to open the relevant pre-defined cursor:
PROCEDURE outer_proc AS
m VARCHAR2(2000):='';
CURSOR c1 IS SELECT first_name,last_name FROM employees WHERE salary<1000;
CURSOR c2 IS SELECT first_name,last_name FROM employees WHERE salary>=1000;
PROCEDURE inner_proc(cur_name IN varchar2,m OUT VARCHAR2) IS
col1 VARCHAR2(20);
col2 VARCHAR2(20);
BEGIN
CASE cur_name
WHEN 'c1' THEN
OPEN c1;
LOOP
FETCH c1 INTO col1,col2; -- this will only work in the cursor only selects two columns!
EXIT WHEN c1%NOTFOUND;
m:=m||col1||' '||col2;
END LOOP;
CLOSE c1;
WHEN 'c2' THEN
OPEN c2;
LOOP
FETCH c2 INTO col1,col2; -- this will only work in the cursor only selects two columns!
EXIT WHEN c2%NOTFOUND;
m:=m||col1||' '||col2;
END LOOP;
CLOSE c2;
END CASE;
END;
BEGIN
inner_proc('c1',m);
inner_proc('c2',m);
END;
/
... but that's not scaleable and arguably already worse than what you have now.
Or pass the cursor query, rather than the cursor, into the procedure, and then open and process it with dynamic SQL. As that is what @Wernfried has demonstrated, I won't go into further detail. The possible downside of that though is that if you already have an explicit cursor that you are trying to reuse then you're now duplicating that query instead. Oh, and as it's dynamic, the query itself won't be parsed until run time, so an error might not be caught as early as you'd like.
TL;DR - see @Wernfried's answer *8-)