Correct a bit on my previous post regarding the "parameterized view".
Missed out on the SYS_CONTEXT. It appears you can emulate the
multi-parameter "parameterized view" with SYS_CONTEXT. Basically for
each parameter you want to pass to the view you use
dbms_session.set_context to set it up in stead. And you create the
view with the multiple sys_context predicates. It seems you can even
use it SYS_CONTEXT to emulate "nested parameterized view". I think you
have to create a context for each layer of the view. But, really...
The setup is long, and when you get to this point, the results look to
me like anything but a paramterized view.
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 - 15:07:14 CST
