Six of one . . .

Platform: Oracle 8.1.7 on NT4

Developer is involved in a project of converting an application (written in Microfocus Cobol) from DB2/2 on OS/2 to Oracle on NT. The general problem at hand is to identify a selection of rows, sorted ascending by a 'timestamp' field (implemented in our 8.1.7 db as a char(26)), processing each row in turn. Processing of each row includes modifying a 'status' column that removes it from the original selection criteria.

In the original application, it is implemented as shown with this pseudo code:

• begin pseudo code example -----

Begin loop

SELECT MIN(CTE_DTS)
 INTO :ws-dts
FROM MY_TABLE
WHERE APPC_STA = 'P'
  AND CTE_DTS > '0001-01-01-00.00.00.000000'

• do a bunch of processing based on the row returned above, then change the status of that row ---

UPDATE my_table
  SET appc_sta = 'Z'
  WHERE APPC_STA = 'P'
       AND CTE_DTS = :ws-dts

commit
End loop
---- end pseudo code example -----

She was having some difficulty with inconsistent results, which I chalked up to inconsistent test cases. (She's a fairly inexperienced programmer, and this is her first exposure to Oracle). By the time I was able to take a look at it, she had replaced the above with a cursor, thusly;

• begin pseudo code example -----

Declare cursor c1 as
  SELECT CTE_DTS
  FROM MY_TABLE
  WHERE APPC_STA = 'P'

        AND CTE_DTS     > '0001-01-01-00.00.00.000000'
  ORDER BY CTE_DTS     

Loop until no-more-rows

   Fetch cursor c1 into . . . .
   Do a bunch of processing
   Update row returned by the fetch
    commit
end loop
---- end pseudo code example -----

So now I'm wondering how to adviser her on the best approach from an Oracle standpoint. Which general approach would be preferred? My gut feel is the cursor loop.

Begin-loop
  SELECT MIN(timestamp-field)
  WHERE condition-1

  UPDATE so that condition-1 is no longer true   COMMIT
End-loop

OR . . .

DECLARE CURSOR
   WHERE condition-1
   ORDER BY timestamp-field

Begin-loop
  FETCH CURSOR
  UPDATE so that condition-1 is no longer true   COMMIT
End-loop

I'm shooting from the hip here, so I may have missed some detail, but if you remember that I'm only presenting pseudo code, the general problem and question should be apparent.

--

Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.) Received on Tue Sep 17 2002 - 14:09:29 CDT