It's O7, I'm afraid, so I think I'm right in saying ref cursors
don't exist... do they?
--
Benetnasch
Remove ".spam.begone"
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
news:9hstlv02g4s_at_drn.newsguy.com...
> In article <Xml07.4424$A51.1022535_at_monolith.news.easynet.net>,
"Benetnasch"
> says...
> >
> >Just wondered whether this is a reasonable way of doing things:
> >
> >I have a PL/SQL procedure which has, as an argument, a flag.
> >If this flag is a 'Y', then I want only records with a value of 17
> >in a specific column to be selected. If the flag isn't "Y", then
> >all records can be selected.
> >
> ><column name> is a number, so I don't really want to TO_CHAR
> >it, as it has an index on it.
> >
> >My code is thus:
> >
> > AND <column name> BETWEEN DECODE(p_newflag
> > , 'Y', 17
> > , 0)
> > AND DECODE(p_newflag
> > , 'Y', 17
> > , 100)
> >
> >(the column is only NUMBER(2), so 100 should be big enough)
> >
>
> well, if the flag is Y I would want to use an index.
>
> if the flag ISN'T Y -- I do not want to use an index (retrieving too many
rows
> from the table to use an index efficiently)
>
> I want two queries. I would use a ref cursor.
>
> is
> type rc is ref cursor;
> l_cursor rc;
> ...
> begin
> if ( p_newflag = 'Y' )
> then
> open l_cursor for select * from t where CNAME = 17;
> else
> open l_cursor for select * from t;
> end if;
>
> loop
> fetch l_cursor into ...;
> exit when l_cursor%notfound;
> ...
> end loop;
>
> close l_cursor;
> end;
>
>
>
> >This is the way I've thought of doing it, but I'd be grateful if anyone
can
> >suggest a better way of doing it.
> >
> >Thanks for any assistance,
> >
> >--
> >Benetnasch
> >Remove ".spam.begone"
> >
> >
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Wed Jul 04 2001 - 01:04:47 CDT
