How do I return the rows from an Oracle Stored Procedure using SELECT?
- by Calanus
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?
