Re: Six of one . . .

"Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message news:3d877d53.112737467_at_ausnews.austin.ibm.com...
>
> 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).

Are the inconsistent results because other sessions are modifying the same table?

> 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.

If you can do it all in a single update then I agree that your code looks nicer, its always better to do set based than procedural based work if you can. The other thing I'd mention is that in each of your examples you commit inside the loop. Its far far better to do the commit outside of the loop. (and if you get snapshot too old thats the DBA's fault right?). It looks to me like one logical transaction so do it in one transaction.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************