Interesting Oracle/JDBC Problem

    I'm working on an extremely high volume archive system, the core of which is an Oracle 7 database. The archive is structured to store items on a daily basis, and creates a new table for every working day. Each table is named according to the unix day on which the table was created. For example, five days worth of tables would look like this in the system table:

arc_10750
arc_10751
arc_10752
arc_10753
arc_10754

    Given this structure, it follows that to search several days worth of tables for an item would involve an SQL UNION statement. For instance, if I wanted to search these tables to find an item in which, say, fuzzy=2, it would require the following statement:

select * from arc_10750 where fuzzy=2 UNION select * from arc_10751 where fuzzy=2 UNION select * from arc_10752 . . .

And so forth. This in itself is not a problem.

The problem arises when we allow fields to be added to next day's table. When we add a field to the schema, the field is included in all future tables.

Say that these are the fields in arc_10750:

key, field1, field2, fuzzy

The next day, we decide to add a field to the archive system. arc_10751, and all later tables, have these fields:

key, field1, field2, fuzzy, newfield

If I execute the above UNION statement across these two tables, I get an Oracle error which tells me that the query block has an invalid number of result columns. This makes sense, since I'm trying to UNION tables with different fields.

So here's the problem: I need to somehow modify this statement to make it work. I need to be able to search across the entire range of tables, regardless of whether later tables have added fields. I'm using the JDBC thin driver to access the db, and I have access to a set of metadata tables which describe what the fields are in a table for any given date.

If anyone has come across a similar situation, I'd appeciate some advice. Is there a way to somehow "widen" a resultset?

I'd appreciate any advice whatsoever.

-Michael Cordner Received on Tue Jun 22 1999 - 23:19:06 CDT