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?

pierre · Accepted Answer · 2010-03-16 12:07:19Z

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, 2010 at 12:07

pierre

1,2451 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Calanus Over a year ago

I did get it to work using this method, but in the end I decided to convert the stored procedure to a function.

Community · Accepted Answer · 2017-05-23 11:57:02Z

0

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

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Mar 16, 2010 at 12:43

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related