Re: parameterized view

not really needed, if you create a view you use a normal WHERE clause when selecting from the view to further restrict it

create view myview as ...

select * from myview where cust_id = 1234

however, if you want to require that a view always be filtered by certain criteria, consider some other possibilities

1. create a package function that is referenced in the view, that returns a value set by a package procedure -- your app must then call the package procedure to set the value used in the view. a little bit of a round-about way to do it, but it forces the app to supply a value in order to retrieve records
2. see MetaLink Note:136909.1 -- PL/SQL Pipelined and Parallel Table Functions in Oracle9i for ways to return a recordset from a stored function
   • mcs

"Steven" <SPAMBLOCKERsteven.pannell_at_gmx.net> wrote in message news:bo5dee$13tr0d$1_at_ID-82797.news.uni-berlin.de...
> Hi,
>
> I'm wondering if it is possible to create some kind of parameterized view
in
> oracle. Like:
>
> create view myview as
> select * from customer_table where cust_id = ?
>
> Then in my query I call
>
> select * from myview where :cust_id = 1234
>
> Probably not huh?
>
> what I'm looking for is a way to store SQL Queries in the database that I
> can change dynamically and not hardcode in the application. parameterized
> views would be great!
>
> Any ideas?
>
> Thanks,
> Steve,
>
>
> --
> -----------------------------------------------------------------
> Holidays in Berchtesgaden, Germany:
> http://www.sonnenkoepfl.de
> http://unterkunft-berchtesgaden.de
> http://pension-berchtesgaden.de
>
>
Received on Mon Nov 03 2003 - 05:36:52 CST