The IN thing

With Oracle 8i and below there was a classic problem that I never really got what I felt was a suitable solution to...

I've always gone for limiting the SQL that exists in both the database and the higher layers, as described (related to PHP)here.

By doing this I find I can very nicely wrap up the database tables with a layer of abstraction that protects the higher layers from changes. So I could have a 'Product' lookup that is based on a reasonably complex query what involves the joining of several tables.
But what if I wanted a list of all the products in the system, or the top 5 priced, or any of those other options that just come up, you've got limited choices.

1. Produce a function that covers each of the lookups, and include the full SQL statement in each lookup.
   
2. Provide a function that covers the return of the list of products ID to return, and pass that list into a function that returns the full details (which can be used for any lookup).
   
3. Provide a single function that does the lookup in any case, and has a lot of parameters.
   
4. Throw away this whole idea of encapsulation and just put the SQL where you need it.
   

I've always liked the second option. You can have functions that cover searching for records and then other functions that deal with the returning the details of those records.

One configuration that allows you to do this is to have the function returning the details deal with a single entity. Pass in the ID of the one you're looking for and get the full set of details back. If you need multiple records, then you call the function multiple times. It's a good solution that's clean and simple. It just doesn't perform well when you scale it up. What happens if I want 1,000 records? I call the function 1,000 times?

The alternative would appear to be to pass in a list of the IDs you want and get back a ref cursor or table of records back.
You can reasonably get back a table of results, but it means looping over the incoming IDs and reverting back to your individual lookup.
It's harder to return a ref cursor though: The problem is the inflexibility of the IN statement. It's not possible for you to specify an arbitrary list of values in a non dynamic SQL statement.
I.E. with vc_list_of_ids set to "1, 2, 3, 4", you can't say:

  OPEN product_cur FOR
    SELECT *
    FROM   product
    WHERE  id IN ( vc_list_of_ids );

although you can say:

  OPEN product_cur FOR
    'SELECT *
     FROM   product
     WHERE  id IN ( '|| vc_list_of_ids ||' )';

It's a subtle difference, but the second (the correct one) will require Oracle to reparse the statement every time it is ran. Ask any DBA and they'll say that's not exactly a great idea.

Thankfully, Oracle 9 gave us a much nicer solution. Casting to tables.
We can now use a PL/SQL table of values and treat it as a ful blown SQL table. So, if we use the table vt_list_of_ids, we can now say:

  OPEN product_cur FOR
    SELECT *
    FROM   product
    WHERE  id IN ( SELECT id FROM TABLE( vt_list_of_ids ) );

This has the advantage of not being re-parsed each time.

If we pass this back as an open ref cursor we need to make sure that the table definition and the variable that contains the data are both available to the outside world, but it works a treat.

I'll put a working example up here once I get a chance...

Technorati Tags: Robert+Baillie, oracle, software+development