Re: I need a dynamic cursor - how?

"DA Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3E78E263.18784369_at_exxesolutions.com...
> If you expect a row to have a column value change after your cursor is
opened
> your design isn't valid. A fetch, by definition, is current to the
point-in-time
> (SCN) when the cursor was opened.
>
> I would suggest you look at using SELECT FOR UPDATE.

My problem isn't that I need to lock the table, the changes that will be made is made by my PL/SQL-procedure. Some of the code (there are some more if-clauses that is not important for the question). The code works fine, except when GjenstMinHon have been moved to the next row, and the sentence marked "mail clause" still believes that GjenstMinHon = 0. If Beholdning = 0 for the updated row, and a row with FDelkontrakt = FDelkontrakt + 1 exists, then GjenstMinHon should be moved to the next row. This does not happen.

Any suggestions?

---
CREATE OR REPLACE PROCEDURE rtyprc_OverforMinHon AS
CURSOR c IS
  SELECT * FROM rtytbl_Verkihonorar;

v_c c%ROWTYPE;
v_opptjentHon rtytbl_verkihonorar.Opptjenthon%TYPE;

BEGIN
  OPEN c;
  LOOP
    FETCH c INTO v_c;
    EXIT WHEN c%NOTFOUND;

    -- Main clause
    IF (v_c.beholdning = 0) AND (v_c.GjenstMinHon > 0) THEN

     -- I'm finding out if there is a row with FDelkontrakt = the current  +
1
      BEGIN
        SELECT OpptjentHon INTO v_opptjentHon FROM rtytbl_verkihonorar
        WHERE FHonorar = v_c.FHonorar AND
              FKontrakt = v_c.FKontrakt AND
              FDelkontrakt = v_c.FDelKontrakt + 1;

        -- If row exists, update the next row with new value of GjenstMinHon
        IF (v_opptjenthon > v_c.GjenstMinHon) THEN
          UPDATE rtytbl_verkihonorar SET MinHon = v_c.GjenstMinHon,
          ForfatterFavor = v_opptjenthon - v_c.GjenstMinHon
          WHERE FHonorar = v_c.FHonorar AND
                FKontrakt = v_c.FKontrakt AND
                FDelkontrakt = v_c.FDelKontrakt + 1;
        END IF;

        --Update the existing row setting GjenstMinHon = 0
        UPDATE rtytbl_Verkihonorar SET GjenstMinHon = 0
        WHERE FHonorar = v_c.FHonorar AND
              FKontrakt = v_c.FKontrakt AND
              FDelkontrakt = v_c.FDelkontrakt;

      -- If selectsentence fails, just continue loop
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;

    END IF;

  END LOOP;
  CLOSE c;

  COMMIT;
END;
/