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

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 Sat Jul 03 1999 - 11:16:34 CDT