-----Original Message-----
Ari,

A while ago I asked your help with embedded C programming and you were
kind enough to reply with a very helpful answer. At the risk of going
once to often to the well, I'd like to ask another, more complicated
question. After scouring the literature, I haven't been able to find a
reliable answer to this.

We are converting a large legacy application to Oracle, and the first
stage (we've determined) is to write an emulator for the existing
database calls. Later, at our leasure, we will convert each module (out
of  nearly 1000) directly to Oracle. One of the old database calls is a
routine that returns the row (record) which is exactly next in logical
key order to a given key.

I have attempted to formalize the problem as shown below. If you have
any ideas or suggestions for study I'd be very grateful.

Bion

bions@usa.net


PROBLEM: Retrieve the NEXT key (step through an index/primary key
row-by-row)

STATEMENT: Given a table with a multiple-column primary key, say,
columns A, B and C taken in that order, is there a way (efficiently,
which presumably rules out cursors) to retrieve the single NEXT row in
key order after a specified key? That is, construct some kind of select
statement with the specific column values A1, B1 and C1, and get in
return just the one row with the key X, Y, Z which is the NEXT logical
key after A1, B1, C1. (NEXT logical would be defined as ((X = A1 and Y =
B1 and Z >> C1) OR (X = A1 and Y >> B1) OR (X >> A1)) where the symbol
>> means the least value greater than. Parsing (and/or row selection) is
assumed to go from left to right and to stop at the first instance when
the result is TRUE.

This can be done in a klugey way with cursors, however, cursors require
Oracle to manipulate lots of rows, and in this application we often want
only one row. Cursors (and arrays) would seem to be very inefficient.
The application requires the equivalent of "stepping through" an index,
row by row, similar to this:

     SET KEY TO starting_key;
    WHILE(not_done) {
                ROW = row_with_next_key_after(KEY);
                 process(ROW)
                KEY = key_of(ROW);
                 }

------------- Reply -------------
It never hurts to ask. I get about 10 questions a day and can usually have time
to answer 2-3. If you ever get a "sorry, I'm too busy" message you'll understand
why.

As for your question, my first thought was using cursors for greater control.
For example, you can use the IF-THEN-ELSE clauses which would be helpful for
finding the next value.
Aside from cursors, you can add a column that has the NEXT key along with each
key. This way you can use Oracle's heirarchy SQL statements (PRIOR, NEXT, and
so on). If you are not familiar with these commands, there are explanations
wherever you read about the EXPLAIN PLAN options. They can even be used to
generate a visual tree structure. You are probably looking for something
simpler for your needs, so this should be sufficient.

Best of luck,

-Ari Kaplan

Back to Ari Kaplan's Home Page t="18"> Back to Ari Kaplan's Home Page