fetch out of sequence 2004-08-23 - By Anthony Molinaro
Paula,
you can leave FOR UPDATE in there (and keep the locks), just commit
outside the loop.
- a
-- --Original Message-- --
From: Paula_Stankus@(protected)
[mailto:Paula_Stankus@(protected)]=20
Sent: Monday, August 23, 2004 11:33 AM
To: Paula_Stankus@(protected); oracle-l@(protected)
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@(protected) '
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@(protected) '
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 (See ORA-01002.ora-code.com): 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@(protected) 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
