RE: fetch out of sequence

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Mon, 23 Aug 2004 11:37:08 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C51D2C2@wgdc02.wgenhq.net>

Paula,
you can leave FOR UPDATE in there (and keep the locks), just commit outside the loop.

-----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:33 AM
To: Paula_Stankus_at_doh.state.fl.us; oracle-l_at_freelists.org Subject: RE: fetch out of sequence

Okay,=3D20

I changed the 'for update' to a simple select and changed the 'where =3D current of' to use license_id. However, I loose grabbing locks on the =
=3D

rows I need in this procedure and could result in the process failing if
=3D I cannot get the locks I need. Any suggestions on the best way to
deal =3D with that?

-----Original Message-----
From: Stankus, Paula G=3D20
Sent: Monday, August 23, 2004 11:15 AM
To: Stankus, Paula G; '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:33:05 CDT