The use of "SELECT MIN(.." is a ancient technique used by languages that
didn't support cursors. Are you still using Microfocus Cobol for the new
system? If yes, then does it support Oracle cursors? If so, you can bag the
"SELECT MIN(..." and use a cursor.
"Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message
news:3d877d53.112737467_at_ausnews.austin.ibm.com...
>
>
> 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 Wed Sep 18 2002 - 08:28:17 CDT
