RE: fetch out of sequence

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Mon, 23 Aug 2004 11:24:33 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C51D2C1@wgdc02.wgenhq.net>

Paula,
  "where current of" does what you think it does, but the problem is not that.
  The problem is in the cleanup_licenses cursor, where you specify FOR UPDATE,
  Which effectively locks those rows for you. Then you are commiting inside that
  cursor loop. You are telling oracle to lock those rows, but then you commit while
  looping through said rows.
  Try moving the commit outside the loop (and close that cursor when you are finished ;)

hope that helps,

-----Original Message-----
From: Paula_Stankus_at_doh.state.fl.us
[mailto:Paula_Stankus_at_doh.state.fl.us]=20 Sent: Monday, August 23, 2004 11:15 AM
To: Paula_Stankus_at_doh.state.fl.us; oracle-l_at_freelists.org Subject: RE: fetch out of sequence

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

PROCEDURE cleanup_licenses IS

    lic_count            NUMBER;
    tmp_issue_date       DATE;
    tmp_expire_date      DATE;

=3D20
CURSOR cs_license_cur IS

      SELECT *
        FROM License_List
       WHERE state_country          =3D3D '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;

=3D20
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            :=3D3D 0;
        tmp_issue_date       :=3D3D NULL;
        tmp_expire_date      :=3D3D NULL;

        SELECT count(*)
          INTO lic_count
          FROM t_fl_lic
         WHERE cs_license_number =3D3D 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 =3D3D 1
        THEN
          SELECT issue_date,
                 expire_date
            INTO tmp_issue_date,
                 tmp_expire_date
            FROM t_fl_lic
           WHERE cs_license_number               =3D3D =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;
        =3D20
          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      =3D3D tmp_issue_date,
                   expire_date     =3D3D tmp_expire_date,
                   timestamp       =3D3D SYSDATE
                   WHERE CURRENT OF cs_license_cur;
          END IF;
        END IF;
       =3D20
        COMMIT;

      END LOOP;

END cleanup_licenses;

-----Original Message-----
From: Stankus, Paula G=3D20
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 =3D little info. It runs successfully in another 9.2.0.4 database I setup =
=3D

on another host. Same database setup, version, processes and =3D 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.
=3D However, I also think the reason it hasn't come up on the other
database =3D environment is that the number of rows would have been = around
100K =3D 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
=3D 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
-----------------------------------------------------------------
----------------------------------------------------------------
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:20:17 CDT