Re: Passing parameters to views...

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 13 Jan 2000 18:59:33 +0800
Message-ID: <387DB015.41B8@yahoo.com>

maylee wrote:
>
> Actually, the kind of parameter I need to pass is at runtime. The date
> range can change at any point, it's not a fixed value.
>
> In article <387D00D4.62151E67_at_assigncorp.com>,
> mbuhari_at_assigncorp.com wrote:
> > Hi,
> >
> > You can always pass parameters to a view ( using "WHERE" predicate ).
> So
> > create a view and U can limit the range by specifying "WHERE" clause
> on that
> > view.
> >
> > Here is an example
> >
> > CREATE OR REPLACE VIEW emp_view
> > AS
> > SELECT a.name,b.dept_name FROM emp a,dept b
> > WHERE a.deptno=b.deptno;
> >
> > SELECT * from emp_view
> > WHERE dept_name='MARKETING';
> >
> > Mohamed.
> >
> > maylee wrote:
> >
> > > I need to create a view for a group of tables based on a range of
> values
> > > (i.e. date ranges)
> > > I was wondering if there is a way to pass parameters to a view, so
> only
> > > this range will be in teh view.
> > >
> > > The underlying tables grow exponentially, so a regular view would
> return
> > > too much data. I need to limit it somehow.
> > >
> > > Thanks,
> > > Maylee
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

You can do it with a package:

create or replace package the_value_of
  some_var number;
  function the_parameter return number; end;
create or replace package body the_value_of   function the_parameter return number is   begin
    return some_var;
  end;
end;

create view parm_view as
select * from table
where some_col = the_value_of.the_parameter;

then you do

begin
  the_value_of.some_var := 10;
end;
/

select * from parm_view;

etc
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Jan 13 2000 - 04:59:33 CST