Re: Ref Cursor question

PMG schrieb:
>
> 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?

With 7.3 you can, wether you can with 7.1 I don't know, since I didn't know Oracle yet at this time.

SELECT

	max(decode(u.field1,...)) a,
	max(decode(u.field1,...)) b,
	...
FROM
	(SELECT 
		*
	FROM
	...	your_union
	) u
WHERE
	blahblah

>
> My last question is actually a confirmation -- that a cursor ref can not
> be generated dynamically in 7.1 or even 7.3.

Yes, it can't.

> Specifically, can I pass in
> a parameter to be used in the WHERE clause?

You can pass a paramter that is used _in_ the WHERE-clause, but not as WHERE-clause:

SELECT
        ...
WHERE
        t.Myfield=p_param1
;
>
> Much appreciated for your help

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Wed Feb 03 1999 - 07:06:00 CST