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

It is nice to have this workaround. But this is really pushing it on tow fronts.

Using dbms_application_info and userenv to emulate "parameterized view" is pushing it a bit. What if you want to pass more than 1 parameter to the view? dbms_application_info.set_client_info takes just 1 parameter. What if view is more than 2 layers deep - view references another view which references yet another view?

Also, with this approach, in stead of keeping everything in PL/SQL, now you have to use SQL to create a real database view object. Again, there will be trouble if the cursors are more than 2 layers deep.

• Jusung Yang

apurba.kalita_at_lycos.com (Apurba Kalita) wrote in message news:<de63e470.0211120217.17e25f34_at_posting.google.com>...
> 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
>
Received on Tue Nov 12 2002 - 12:05:44 CST