RE: fetch out of sequence
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Aug 2004 11:37:08 -0400
Paula,
you can leave FOR UPDATE in there (and keep the locks), just commit
outside the loop.
- a
-----Original Message-----
From: Paula_Stankus@xxxxxxxxxxxxxxx
[mailto:Paula_Stankus@xxxxxxxxxxxxxxx]=20
Sent: Monday, August 23, 2004 11:33 AM
To: Paula_Stankus@xxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
