I changed the 'for update ' to a simple select and changed the 'where =
current of ' to use license_id. However, I loose grabbing locks on the =
rows I need in this procedure and could result in the process failing if =
I cannot get the locks I need. Any suggestions on the best way to deal =
with that?
-- --Original Message-- --
From: Stankus, Paula G=20
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 =
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@(protected) '
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:
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@(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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
