Re: Ref Cursor question

From: PMG <pete_g_at_2xtreme.net>
Date: Sun, 07 Feb 1999 05:41:42 GMT
Message-ID: <36BD2791.8E7D721F@2xtreme.net>

Thank you for your reply, Mathias.

However, first of all, I get an error msg, PLS-00320, when I try to run the following statement ( even though the SELECT statement works fine in SQLPLUS):

CREATE OR REPLACE PROCEDURE RETURN_RESULT_SET    (oCursor IN OUT MyPkg.CursorType) as BEGIN
   open oCursor for
 select max(decode(hits,null, null, hits)),

  max(decode(cnt_hits, null, null, cnt_hits)),
  max(decode(smg, null, null, smg)),
  max(decode(cnt_smg, null, null, cnt_smg))

 from
 (select oth_type HITS, count(*) CNT_HITS, NULL SMG, TO_NUMBER(NULL) CNT_SMG  from hs_xref
 where oth_type = 'HITS'
 group by oth_type
 UNION ALL
 select NULL HITS, TO_NUMBER(NULL) CNT_HITS, oth_type SMG, count(*) CNT_SMG  from hs_xref
 where oth_type = 'SMG'
group by oth_type);
END;
/

And secondly, pls clarif your statement that

    You can pass a paramter that is used _in_ the WHERE-clause, but not as WHERE-clause:
Perhaps an example of this using a cursor type. My goals is a procedure of this type:

CREATE OR REPLACE PROCEDURE RETURN_RESULT_SET    (oCursor IN OUT MyPkg.CursorType,

     AColumn IN VARCHAR,
     AValue    IN VARCHAR) as

BEGIN
   open oCursor for

         select * from ATable where AColumn like AValue||% END; Matthias Gresz wrote:

> 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 Sat Feb 06 1999 - 23:41:42 CST