Re: Parameter Query in Oracle?

I'm a little familliar with the Oracle environment, but no pro. The environment you are working with probably has a shell (PL/SQL etc.) that you can use to prompt for input and execute a query based on that input.

Let's say you have user's enter "ALL" if they want to have no restricions.

(this is not syntactically correct, just psuedo code. you can research the syntax for your specific purposes.)

In the shell :
<UserPrompt> = "Enter ctriteria:"

If <UserPrompt> = "ALL" then

    SELECT * FROM Places;
Else

    SELECT * FROM Places WHERE Place_Type = <UserPrompt> End If

Ther is a way to put the <UserPrompt> right in the Where clause, but you would not be able to run without restrictions if you put it there.

Hope this helps, good luck.

Mark

Edgar Walther wrote:

> Hi y'all,
>
> I use MS Access to generate reports from an Oracle 7.3 database.
> For performance reasons I want to make a parameter query in Oracle.
>
> It should do something like this:
>
> SELECT *
> FROM Places
> WHERE Place_Type = <parPlaceType>
>
> I also want to be able to have the query ignore the parameter if it is not
> filled in (adding like * to it is not what I need).
>
> How can I do this? Should I make a view with parameters? Should I do
> something with stored procedures? Dynamically build the SQL string for an
> SQL PassThrough query?
>
> Can anyone help me with this?
>
> TIA,
>
> Edgar
Received on Thu Aug 26 1999 - 00:00:00 CEST