I have an existing stored procedure that takes 2 parameters and returns back an oracle cursor. The cursor contains about 30 to 60 rows of data.
I want to use the above pre-existing stored procedure in another stored procedure as a table…basically I want to call the pre-existing stored procedure and see if the rows returned back contain a particular value.
For example:
SP 1 = get_data_1 (returns oracle cursor)
SP 2 = get_data_2
in get_data_2
select count(*) from get_data_1 (pass_input_parms) A where A.ID = '12345'
Conceptually it seems like a trivial thing to do to me however, being new to the oracle world I do not know how to make use of preexisting stored procedures that return cursors.
How would I do this?
Best Answer
You cannot reuse a REF CURSOR from get_data_1 in a subsequent SQL statement because it's just a pointer to a statement handle. The cursor itself contains no data.
You could do something like
As you might imagine, though, this tends to get old relatively quickly. Given that, it tends not to be common in Oracle to have stored procedures that return REF CURSOR parameters except in cases where you are returning a finished view of the data to a client application. If there was a shared view, for example, that both GET_DATA_1 and GET_DATA_2 could query rather than having GET_DATA_2 call GET_DATA_1, that would simplify the program. If GET_DATA_1 was a pipelined table function rather than a procedure that returned a REF CURSOR, then it would be much easier to call GET_DATA_1 from GET_DATA_2.
If you want to get started with pipelined table functions (using the SCOTT schema)