How can I fetch the data from the SYS_REFCURSOR from one stored proc and use it in another?
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;