Re: Ref Cursor question

PMG schrieb:
>
> 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;
What will work is:

 CREATE OR REPLACE PROCEDURE RETURN_RESULT_SET     (oCursor IN OUT MyPkg.CursorType,
      AValue IN VARCHAR) as
 BEGIN
    open oCursor for

          select * from ATable where YourColumn like AValue||'%';  END; That's what I wnated to say with:

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

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

--
Matthias.Gresz_at_Privat.Post.DE

Es gibt nichts Neues mehr.
Alles, was man erfinden kann, ist schon erfunden worden. Charles H. Duell, Leiter des US Patentamtes bei seinem Rücktritt 1899 Received on Mon Feb 08 1999 - 01:05:09 CST