Re: can I use a pl/sql cursor as subquery or in another cursor?

try this option...

customer_at_HL163DV1> create table tababc
  2 (a number(10),

  3   b varchar2(20),
  4   c varchar2(30),
  5   d varchar2(40));

Table created.

customer_at_HL163DV1> ed
Wrote file afiedt.buf

  1* insert into tababc values (1,'b1','c1','d1') customer_at_HL163DV1> /

1 row created.

customer_at_HL163DV1> ed
Wrote file afiedt.buf

  1* insert into tababc values (1,'b2','c2','d1') customer_at_HL163DV1> /

1 row created.

customer_at_HL163DV1> ed
Wrote file afiedt.buf

  1* insert into tababc values (1,'b3','c3','d1') customer_at_HL163DV1> /

1 row created.

  1* insert into tababc values (2,'b4','c1','d1') customer_at_HL163DV1> /

1 row created.

customer_at_HL163DV1> commit;

Commit complete.

customer_at_HL163DV1> ed
Wrote file afiedt.buf

  1 create or replace view abcview
  2 as
  3 select * from tababc
  4* where d = userenv('client_info')
customer_at_HL163DV1> /

View created.

  1 declare
  2 cursor c_test is select c,sum(a) sum from abcview group by c;   3 begin
  4 dbms_application_info.set_client_info('d1');   5 for v_rec in c_test loop
  6 dbms_output.put_line ('c=' || v_rec.c || 'sum=' || v_rec.sum);   7 end loop;
  8* end;
customer_at_HL163DV1> /

c=c1sum=3
c=c2sum=1
c=c3sum=1

I read about parameterized views in
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1448404423206 There he talks about using dbms_session.set_context for increasing the number of parameters. I am working in a very very restricted environment and dont have privileges to create context ((:- So could not try that out.

Regards,
Kalita

Matthias Rogel <rogel_at_web.de> wrote in message news:<aqnjkm$bl5ir$1_at_ID-86071.news.dfncis.de>...
> Well, I think, the problems with views is that there isn't a thing like
> parameterized views.
>
> I hope with Peter, that somedays, there will ...
> (@ least in Oracle)
>
> Matthias
>
>
> Robbert Van der Hoorn wrote:
> > May be you could try to use views...
> >
> >
> > "Peter Mutsaers" <plm_at_gmx.li> schreef in bericht
> > news:873cqcm3vd.fsf_at_muon.mutsaers.com...
> >
> >>Is it possible (in oracle 9i) to somehow use a cursor as subquery or
> >>in another cursor, in order to reuse parts of queries or to build
> >>complex queries modularly without resorting to dynamic sql?
> >>
> >>I thought maybe oracle 9i new pipelined functions or cursor() operator
> >>might provide something, but I couldn't really figure out how.
> >>
> >>I'd like something like this (in a package):
> >>
> >>cursor abc(p1 in varchar2) is select a,b,c from tababc where d = p1;
> >>
> >>cursor sumabc(p1 in varchar2) is
> >> select a,sum(c) from (abc(p1)) group by a;
> >>
> >>
> >>It really drives me mad (being used to other computer languages where
> >>you can build using bottom-up or top-down breakdown) that I don't know
> >>how to avoid copying blocks of SQL for such cases. It must be possible
> >>in some way (I hope) to modularize sql statements???
> >>
> >>
> >>--
> >>Peter Mutsaers, Dübendorf, Switzerland.
> >
> >
> >
Received on Tue Nov 12 2002 - 04:17:18 CST