Ref Cursor question

This is a multi-part question.

I am trying to "flatten" out a query -- that is rotating rows to columns (I forget the technical term for it). Anyway, it's a two step process, first a query consisting of a series of unions, and then a query which does a series of MAX(DECODE(...)) statements.

I would like to incorporate this into a stored procedure in order to speed it up, hopefully.

My questions are: 1). Given the standard way of using Ref Cursors, CREATE PACKAGE MYPKG
IS
 TYPE CursorType IS REF CURSOR;
END MyPkg;
/
CREATE PROCEDURE RETURN_RESULT_SET
   (oCursor IN OUT MyPkg.CursorType) AS BEGIN
   open oCursor for select * from employees; END; can one modify this to first generate a cursor holding the intermediate query, and then have oCursor refer to the first cursor? Can this be done in 7.1? 7.3?

My last question is actually a confirmation -- that a cursor ref can not be generated dynamically in 7.1 or even 7.3. Specifically, can I pass in a parameter to be used in the WHERE clause?

Much appreciated for your help Received on Wed Feb 03 1999 - 01:06:03 CST