How do I return the rows from an Oracle Stored Procedure using SELECT?

Question

I have a stored procedure which returns a ref cursor as follows:

CREATE OR REPLACE PROCEDURE AIRS.GET_LAB_REPORT (ReportCurTyp OUT sys_refcursor)
AS
   v_report_cursor   sys_refcursor;
   report_record     v_lab_report%ROWTYPE;
   l_sql             VARCHAR2 (2000);
BEGIN
   l_sql := 'SELECT * FROM V_LAB_REPORT';

   OPEN v_report_cursor FOR l_sql;

   LOOP
      FETCH v_report_cursor INTO report_record;

      EXIT WHEN v_report_cursor%NOTFOUND;
   END LOOP;

   CLOSE v_report_cursor;
END;

I want to use the output from this stored procedure in another select statement like:

SELECT * FROM GET_LAB_REPORT()

but I can't seem to get my head around the syntax.

Any ideas?

Answer 1

Whenever I've had to do this; I've used the Oracle TYPE and CAST features.

Something like:

SELECT *
FROM TABLE(CAST(F$get_Cassette_Tracking('8029241') AS cass_tracking_tab_type))

You need to setup the TYPE and all the columns you need and have them use:

pipe ROW(out_obj)

to capture your data. There are many ways to do this and if I can dig out a better example I will but this might give you an idea.

Answer 2

Hi Calanus,

See this SO for a working example: Oracle Parameters with IN statement?