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
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