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

Posted by Calanus on Stack Overflow See other posts from Stack Overflow or by Calanus
Published on 2010-03-16T11:50:22Z Indexed on 2010/03/16 12:46 UTC
Read the original article Hit count: 246

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about plsql