I have a stored proc with the basic layout below that returns a sys_refcursor as a result set. (Technically it reurns four but for the sake of clarity I just say one). The result set is a selection from a temp table.
( C1 in out sys_refcursor
--populate Temp_Table here with a stored proc call;
OPEN C1 FOR
Assuming that the caller knows the structure of the cursor that
aProcedure is opening, you can do something like this.
declare l_rc sys_refcursor; l_rec temp_table%rowtype; begin aProcedure( l_rc ); loop fetch l_rc into l_rec; exit when l_rc%notfound; dbms_output.put_line( l_rec.col1 ); end loop; close l_rc; end; /
If you can't fetch into a record type, you can also fetch into a number of other scalar local variables (the number and type have to match the number and type of columns that
aProcedure specifies in its
SELECT list). In my case, I defined
aProcedure to return two numeric columns
declare l_rc sys_refcursor; l_col1 number; l_col2 number; begin aProcedure( l_rc ); loop fetch l_rc into l_col1, l_col2; exit when l_rc%notfound; dbms_output.put_line( l_col1 ); end loop; close l_rc; end;