SQL & PL/SQL

Hi,

It may sound a bit silly judging by the thread's subject, but I think the question is legitimate. Here's my problem:

We have a large table that stores the chronicles for certain flight metrics over time. These are stored in the form of varchar fields with the following syntax:

360:3|200:20|100:50

a value like this in a column for seats sold would translate into 3 seats being sold 360 days before the flight departure, 20 seats at 200 days before departure and 50 seats a 100 days before. Whether it makes sense to do it like this is out of scope of this thread - I have no influence over it. The data volume goes into millions of rows.

In the java app that operates over this database there's a scenario, where for a future flight we need to find the corresponding chronicle entry from a year before, and calculate some integer values that basically boil down to linear interpolation of the values from the chronicle. Concrete example would be the number of seats sold 150 days before departure, because today is 150 days before the departure of the future flight in question. This calculated value is then updated into the table for current flights, as well as a few other metrics calculated in the same fashion.

Question is this. I don't really need this data in the java code (service method that will perform this logic is actually supposed to return void), so making a select, calculating stuff in java, and making an update seems to make little sense to me compared to a single update query with a bunch of columns (correlated update). The tricky part is the funny way of storing the chronicles - for that I'd need a stored procedure to calculate the interpolated value. As I found so far, Oracle does not seem to be particularly strong on the subject of parsing strings, but then again, I haven't found anyone in similar circumstances. So the two choices are:

1) do select -> send the results to the java app (network overhead) -> calculate the metrics in java -> send update (another network overhead)
2) write a stored procedure that will split the string and do the calculation, and do the update in place in one query using that procedure.

I'm tempted to say option two makes more sense, but I'm not an expert on PL/SQL. It's also worth taking into consideration, that this logic will be used quite often, though I cannot give even a rough estimate of the number of requests per second. Another factor is that the client seems allergic to having logic implemented in oracle, but at the same time wants top notch performance... Any advice would be welcome, I have a meeting possibly also about this subject in an hour :)