userimage

refcursor as input parameter

Hi,

can we make refcursor as input parameter in a procedure.

If yes how to write it. Please let me know.

Thanks in advance        

userimage
Yes, we make refcursor as input parameter in a procedure and to fetch from in in that procedure.

In very general lines it looks like this:

CREATE OR REPLACE PROCEDURE my_proc (p_cursor IN SYS_REFCURSOR)
AS
-- define variables to fetch data into them
var1 ...
var2 ...
BEGIN
LOOP
FETCH p_cursor INTO var1, var2 ...
EXIT WHEN p_cursor%NOTFOUND;
-- process data as needed
END LOOP;
CLOSE p_cursor;
END;
/


Then we can call the procedure by passing to it an OPEN ref cursor, like this:

DECLARE
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR SELECT ... ;

my_proc (l_cursor);
END;
/


or, you can use a CURSOR expression, that will open the cursor automatically for you:

BEGIN
my_proc ( CURSOR (SELECT ... ) );
END;
/

Answer is