Picking query based on parameter in Oracle PL/SQL

Question

Ok, say I have a query:

SELECT * FROM TABLE_AWESOME WHERE YEAR = :AMAZINGYEAR;

Which works very nicely. But say I want to be able to return either just those results or all results based on a drop down. (e.g., the drop down would have 2008, 2009, ALL YEARS)

I decided to tackle said problem with PL/SQL with the following format:

DECLARE
  the_year VARCHAR(20) := &AMAZINGYEAR;
BEGIN
  IF the_year = 'ALL' THEN
      SELECT * FROM TABLE_AWESOME;
  ELSE
      SELECT * FROM TABLE_AWESOME WHERE YEAR = the_year;
  END IF;
END;

Unfortunately, this fails. I get errors like "an INTO clause is expected in this SELECT statement".

I'm completely new to PL/SQL so I think I'm just expecting too much of it. I have looked over the documentation but haven't found any reason why this wouldn't work the way I have it. The query I'm actually using is much much more complicated than this but I want to keep this simple so I'll get answer quickly.

Thanks in advance :)

Answer 1

There is a real danger in the queries offered by Jim and Alex.

Assumption, you have 20 years of data in there, so a query on YEAR = return 5% of the blocks. I say blocks and not rows because I assume the data is being added on that date so the clustering factor is high.

If you want 1 year, you want the optimizer to use an index on year to find those 5% of rows.

If you want all years, you want the optimizer to use a full table scan to get every row.

Are we good so far?

Once you put this into production, the first time Oracle loads the query it peaks at the bind variable and formulates a plan based on that.

SO let's say the first load is 'All'.

Great, the plan is a Full table scan (FTS) and that plan is cached and you get all the rows back in 5 minutes. No big deal.

The next run you say 1999. But the plan is cached and so it uses a FTS to get just 5% of the rows and it takes 5 minutes. "Hmmm... the user says, that was many fewer rows and the same time." But that's fine... it's just a 5 minute report... life is a little slow when it doesn't have to be but no one is yelling.

That night the batch jobs blow that query out of the cache and in the morning the first user asks for 2001. Oracle checks the cache, not there, peeks at the variable, 2001. Ah, the best plan for that is an index scan. and THAT plan is cached. The results come back in 10 seconds and blows the user away. The next person, who is normally first, does the morning "ALL" report and the query never returns.

WHY?

Because it's getting every single row by looking through the index.... horrible nested loops. The 5 minute report is now at 30 and counting.

Your original post has the best answer. Two queries, that way both will ALWAYS get the best plan, bind variable peeking won't kill you.

The problem you're having is just a fundamental Oracle issue. You run a query from a tool and get the results back INTO the tool. If you put a select statement into a pl/sql block you have to do something with it. You have to load it into a cursor, or array, or variable. It's nothing to do with you being wrong and them being right... it's just a lack of pl/sql skills.

Answer 2

You could do it with one query, something like:

SELECT * FROM TABLE_AWESOME WHERE (? = 'ALL' OR YEAR = ?)

and pass it the argument twice.

Answer 3

In PL/SQL you have to SELECT ... INTO something, which you need to be able to return to the client; that could be a ref cursor as tanging demonstrates. This can complicate the client.

You can do this in SQL instead with something like:

SELECT * FROM TABLE_AWESOME WHERE :AMAZING_YEAR = 'ALL' OR YEAR = :AMAZINGYEAR;

... although you may need to take care about indexes; I'd look at the execution plan with both argument types to check it isn't doing something unexpected.

Answer 4

Not sure about using a SqlDataSource, but you can definately do this via the system.data.oracle or the oracle clients.

You would do this via an anonymous block in asp.net

VAR SYS1 REFCURSOR;
VAR SYS2 REFCURSOR;

DECLARE
  FUNCTION CURSORCHOICE(ITEM IN VARCHAR2) RETURN SYS_REFCURSOR IS
      L_REFCUR SYS_REFCURSOR;
    returnNum VARCHAR2(50);
    BEGIN
        IF upper(item) = 'ALL' THEN
            OPEN L_REFCUR FOR
            SELECT  level  FROM DUAL 
            CONNECT BY LEVEL < 15 ;
        ELSE
            OPEN L_REFCUR FOR
            SELECT   'NONE'  FROM DUAL ;  
        END IF;
        RETURN L_REFCUR;
    END ;
BEGIN
:SYS1 := CURSORCHOICE('ALL');
:SYS2 := CURSORCHOICE('NOT ALL');
end ;
/
PRINT :SYS1 ;
PRINT :SYS2 ;

whereas you would simply create an output param (of type refcursor) -- instead of the var sys# refcursors) and pretty much just amend the above code.

I answered a similar question about getting an anonymous block refcuror here How to return a RefCursor from Oracle function?

Answer 5

This kind of parameter shall be processed from within your code so that your OracleCommand object only executes either queries.

using (var connection = new OracleConnection(connString)) {
    connection.Open();

    string sql = "select * from table_awesome";
    sql = string.Concat(sql, theYear.Equals(@"ALL") ? string.Empty : " where year = :pYear")

    using (var command = connection.CreateCommand()) {
        command.CommancText = sql;
        command.CommandType = CommandType.Text;
        var parameter = command.CreateParameter();
        parameter.Name = @":yearParam";
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = theYear;

        var reader = command.ExecuteQuery();

        if (!reader.HasRows) return;

        while (reader.Read()) {
            // Extract your data from the OracleDataReader instance here.
        }
    }
}