Re: The Oracle-No-Parameterized-View mystery ...

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 28 Jun 2006 04:40:44 -0700
Message-ID: <1151494844.326829.242870@75g2000cwc.googlegroups.com>

Mark C. Stock wrote:
> "Martin T." <bilbothebagginsbab5_at_freenet.de> wrote in message
> news:1151476478.707538.43140_at_y41g2000cwy.googlegroups.com...
> : Mark C. Stock wrote:
> : > "Martin T." <bilbothebagginsbab5_at_freenet.de> wrote in message
> : > news:1151413920.975720.195890_at_y41g2000cwy.googlegroups.com...
> : > :
> : > : Simple Question: Why does Oracle not support parameters to views?
> : >
> :
> (...)
> IIUC, you would want to be able to submit something like:

>


>     select * from myview(15, 'OPEN', date '2006-01-22');

>

> Rather than

>


>     set_params_myview(15, 'OPEN', date '2006-01-22'); select * from myview;

>

> Or

>


>     select * from myview

>     where x = 15

>     and y = 'OPEN'

>     and x between date '2006-01-22' and sysdate;

>

> The 1st approach (the one you're advocating, if i've got it right) would
> require a single object and a single call

>

> The 2nd approach requires creating and maintaining 2 objects (the procedure
> to set the parameters in the environment, plus the view) and 2 calls

>

> The 3rd approach may require putting some of the logic in the application
> code to handle different variations of parameters;

>

> I can see some value in passing parameters to a view as one would pass then
> to a procedure or function. Without spending a great deal of time with it,
> it seems that something like that could be accomplished with pipeline
> functions or objects, but a parameterized view could simpler -- but probably
> more limited that pipeline functions.

>

You very nicely stated the 3 alternatives :)

As I already mentioned there are a number of alternatives in Oracle that will achive pretty much the same result as a directly, syntactically, parameterized view .
>From a functional point of view they seem equivalent to a par.view . (Well, pipeline functions seem to be much more powerful, but one could achive the desired results.)

And exactly because I believe that Oracle already provides the _functionality_ of parameterized views via several ways, it is a mystery to me why it does not support a direct syntax for it ...

best,
Martin Received on Wed Jun 28 2006 - 06:40:44 CDT