RE: fetch out of sequence

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Mon, 23 Aug 2004 11:14:44 -0400
Message-ID: <80D4A99A2715674EB2D256DAD89219F6044E8C67@dohsmail02.doh.ad.state.fl.us>

I seemed to have located the procedure with the problem. From what I am = reading this error is related to "selecting from a for update cursor" = after a commit. The procedure does have a for update cursor - however, = it also uses "where current of" in the update - sooo - why would it be a = problem. Doesn't the "where current of" ensure that I am only updating = a specific row?

PROCEDURE cleanup_licenses IS

    lic_count            NUMBER;
    tmp_issue_date       DATE;
    tmp_expire_date      DATE;

=20

    CURSOR cs_license_cur IS

      SELECT *
        FROM License_List
       WHERE state_country          =3D 'FL'
         AND license_status_id      IS NOT NULL
         AND license_activity_id    IS NOT NULL
         AND original_issue_date    IS NOT NULL
         AND license_number         NOT IN ('appl0', 'CH0')
         FOR UPDATE;

    cs_license         cs_license_cur%ROWTYPE;

=20

    BEGIN

      OPEN cs_license_cur;
      LOOP -- for each license row in license_list
        FETCH cs_license_cur INTO cs_license;
        EXIT WHEN cs_license_cur%NOTFOUND;
        --
        -- get corresponding t_fl_lic row for comparison
        --
        lic_count            :=3D 0;
        tmp_issue_date       :=3D NULL;
        tmp_expire_date      :=3D NULL;

        SELECT count(*)
          INTO lic_count
          FROM t_fl_lic
         WHERE cs_license_number =3D cs_license.license_number
           AND TO_CHAR(license_status_id)      IS NOT NULL
           AND TO_CHAR(activity_status_id)     IS NOT NULL
           AND orig_issue_date                 IS NOT NULL;

        IF lic_count =3D 1
        THEN
          SELECT issue_date,
                 expire_date
            INTO tmp_issue_date,
                 tmp_expire_date
            FROM t_fl_lic
           WHERE cs_license_number               =3D =
cs_license.license_number
             AND TO_CHAR(license_status_id)      IS NOT NULL
             AND TO_CHAR(activity_status_id)     IS NOT NULL
             AND orig_issue_date                 IS NOT NULL;
        =20
          IF (   tmp_issue_date  <> cs_license.issue_date
              OR tmp_expire_date <> cs_license.expire_date)
          THEN
          --
          -- new dates - update license_list row and and continue
          --
            UPDATE License_List
               SET issue_date      =3D tmp_issue_date,
                   expire_date     =3D tmp_expire_date,
                   timestamp       =3D SYSDATE
                   WHERE CURRENT OF cs_license_cur;
          END IF;
        END IF;
       =20
        COMMIT;

      END LOOP;

  END cleanup_licenses;

-----Original Message-----
From: Stankus, Paula G=20
Sent: Monday, August 23, 2004 9:13 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: fetch out of sequence

Guys,

I have a database and packages/procs, I have recently inherited with = little info. It runs successfully in another 9.2.0.4 database I setup = on another host. Same database setup, version, processes and = procedures. However, I am getting a specific error message:

ORA-01002: fetch out of sequence

My thinking is that it likely is an issue with how the proc. is coded. = However, I also think the reason it hasn't come up on the other database = environment is that the number of rows would have been around 100K = versus 800K (due to a delay in running this nightly batch process).

Any suggestions on what to look for in the code with the specific error = above could the number of rows make the difference?

Thanks,
Paula

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Aug 23 2004 - 10:11:42 CDT