Re: Views With Parameters or some such solution???

Good idea hadn't thought of that, I don't think you need the select from dual sub-query though

i.e.

create view myview
as
select * from T where c1 = my_parms.get_parm;

instead of

create view myview
as
select * from T where c1 = ( select my_parms.get_parm from dual );

Thomas Kyte wrote in message <37823648.5629645_at_newshost.us.oracle.com>...
>On Sat, 3 Jul 1999 23:35:00 +1000, you wrote:
>
>>Only by creating a table and putting the parameters in it (by doing this
you
>>can also make the same view return a different subset of records for
>>different users)
>>
>
>you can do it with a table by using a package. consider:
>
>create or replace package my_parms
>as
> procedure set_parm( p_value in varchar2 );
> function get_parm return varchar2;
> pragma restrict_references( get_parm, wnds, rnds, wnps );
>end;
>/
>
>create or replace package body my_parms
>as
>
>g_parm varchar2(255);
>
>procedure set_parm( p_value in varchar2 )
>is
>begin
> g_parm := p_value;
>end;
>
>function get_parm return varchar2
>is
>begin
> return g_parm;
>end;
>
>end;
>/
>
>
>Now you can:
>
>create view myview
>as
>select * from T where c1 = ( select my_parms.get_parm from dual );
>
>
>and then:
>
>SQL> exec my_parm.set_parm( 'X' );
>SQL> select * from myview
>
>
>
>In Oracle8i, release 8.1 there is an Application Context which makes this
even
>easier. If you are interested in seeing what application contexts are,
follow
>my url below and read the article on fine grained access control
>
>
>>i.e. have a table VIEW_PARAMS with columns USERNAME, LOWERLIMIT,
UPPERLIMIT
>>
>>and a view along the lines of
>>
>>SELECT SOMETHING FROM SOME TABLE WHERE SOMEVALUE BETWEEN
>>VIEW_PARAMS.LOWERLIMIT AND VIEW_PARAMS.UPPERLIMIT AND USER =
>>VIEW_PARAMS.USERNAME
>>
>>Dave Waterworth
>>
>>jairamk_at_geocities.com wrote in message <7ldhno$jc3$1_at_nnrp1.deja.com>...
>>>Hi! All!
>>>
>>> i am working on an application which requires the creation of a view
>>>joining 3 large tables (outer join). These three tables are growing
>>>daily... so i am afraid that if i use this view it will result in
>>>cartesian products being performed for all records... i would like to
>>>try and limit this by being able to pass a key value as a parameter
>>>to the view query... is it possible? if not is there any other way i can
>>>try and get around this problem... i am using Oracle 8.0.4 on Solaris
>>>
>>>thanks in advance
>>>regards
>>>jairam kuppuswamy
>>>
>>>
>>>Sent via Deja.com http://www.deja.com/
>>>Share what you know. Learn what you don't.
>>
>
>
>--
>See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
>Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
>Thomas Kyte tkyte_at_us.oracle.com
>Oracle Service Industries Reston, VA USA
>
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Sun Jul 04 1999 - 02:38:13 CDT