Re: Implicit Passing of "Parameters" to Views

From: <michael_bialik_at_my-deja.com>
Date: Tue, 31 Aug 1999 20:03:34 GMT
Message-ID: <7qhcee$618$1@nnrp1.deja.com>

Hi.

 I encountered the same problem a couple of month ago.  Posted a request, asked tech. support - but no elegant  solution.
 Eventually I did following :

1. Created a package : CREATE PACKAGE v_parm IS PROCEDURE set_value ( p_v IN Table1.field2%TYPE ); FUNCTION get_value RETURN Table1.field2%TYPE; PRAGMA RESTRICT REFERENCES ( get_value,WNDS,WNPS ); END; CREATE PACKAGE BODY v_parm AS t_val Table1.field2%TYPE := NULL; PROCEDURE set_value ( p_v IN Table1.field2%TYPE ) IS BEGIN t_val := p_v; END set_value;

     FUNCTION get_value RETURN Table1.field2%TYPE IS
     BEGIN
       RETURN t_val;
     END get_value;

    END;   2. The view looked that way :

     Create View View1 as
       Select A.field1,A.field2,sum(A.field3)
       From Table1 A
       WHERE A.field2 = v_parm.get_value;

  3. Before making a SELECT from view in application -
     I called
      v_parm.set_value ('Parm_value');

  Verrrrry ugly by-pass, but it works.

  Good luck. Michael.

In article <7qekhn$2mv$1_at_athena.netset.com>,   "Tim Theis" <ttheis_at_dytn.veridian.com> wrote:
> I have a view set up like...
>
> Create View View1 as
> Select A.field1,A.field2,sum(A.field3)
> From
> Table1 A
>
> I now want to issue a select statement on this view similar to the
> following...
>
> Select * from View1 where field2 = 'PARAMETER'
>
> and have the underlying view1 know to restrict the query to only those
> records where field2='Parameter'. All of my experimentation seems to
prove
> that Oracle creates a temporary table of all possible values first,
and then
> restricts the rows in the temporary table to those meeting the
condition.
> Since this is a very large table, it is very inefficient.
>
> Any help would be appreciated. I am using Oracle 7.3.
>
> Tim Theis
> ttheis_at_dytn.veridian.com
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Aug 31 1999 - 15:03:34 CDT