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?

score 1 · Accepted Answer · answered Mar 16 '10 at 12:07

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.

answered Mar 16 '10 at 12:07

pierre

1,235
1
13
30

I did get it to work using this method, but in the end I decided to convert the stored procedure to a function. – Calanus Mar 16 '10 at 18:45

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

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

edited May 23 '17 at 11:57

Community

1
1

answered Mar 16 '10 at 12:43

Vincent Malgrat

66,725
9
119
171

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

2 Answers2