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

"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?
: >
: > if many people would like to have it, they should submit enhancement
: > requests to oracle
: >
: Hm. Well ... maybe not so many :)
: I really have no empirical evidence on this - it's just that I found a
: bunch of threads where people asked how to do it in Oracle.
:
: > are you asking from an MS-Access perspective? in that environment the
: > interface is tightly couple to the DB engine, so a parameterized (query)
: > 'makes sense', since the gui can prompt the user
: >
: No, I do not use MS-Access. Oracle is the first Database I work with.
: (Apart from mysql or postgre)
:
: > it really doesn't make sense in an RDBMS, since the RDBMS does not
control
: > the GUI, and each GUI would have to have it's own way to respond to some
: > sort of message or exception from the database, then continue the
operation
: >
:
: I'm sorry. I do not understand what parameters to views have to do with
: the GUI.
:
: If my 4 applications have to do a complex query at 10 different
: locations I would naturally 'hide' this complex query in a view. If I
: can supply parameters to the view, then I can put more complexity into
: the view instead of the point where it's used, so my applications will
: be easier to maintain.
:
: best,
: Martin
:

My apologies for assuming the MS-Access context (it's the only place i've seen parameters in views or queries, and the gui detects them and automatically prompts) -- bad assumption on my part, I should have asked for an example of usage.

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.

++ mcs Received on Wed Jun 28 2006 - 05:56:49 CDT