From oracle-l-bounce@freelists.org Mon Aug 23 10:29:17 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i7NFTHS26935 for ; Mon, 23 Aug 2004 10:29:17 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i7NFTGI26926 for ; Mon, 23 Aug 2004 10:29:16 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B0FC372D104; Mon, 23 Aug 2004 10:33:25 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26119-77; Mon, 23 Aug 2004 10:33:25 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4FE0B72D112; Mon, 23 Aug 2004 10:33:24 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 23 Aug 2004 10:31:50 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4E04572CE15 for ; Mon, 23 Aug 2004 10:31:50 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26641-19 for ; Mon, 23 Aug 2004 10:31:50 -0500 (EST) Received: from irm00smail02.doh.ad.state.fl.us (irm00smail02.doh.state.fl.us [167.78.1.36]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A1E5672CE11 for ; Mon, 23 Aug 2004 10:31:49 -0500 (EST) Received: from dohsmail02.doh.ad.state.fl.us ([167.78.1.74]) by irm00smail02.doh.ad.state.fl.us with Microsoft SMTPSVC(5.0.2195.6713); Mon, 23 Aug 2004 11:33:26 -0400 X-MIMEOLE: Produced By Microsoft Exchange V6.0.6556.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Subject: RE: fetch out of sequence Date: Mon, 23 Aug 2004 11:33:25 -0400 Message-ID: <80D4A99A2715674EB2D256DAD89219F6044E8C6B@dohsmail02.doh.ad.state.fl.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: VPD Pre/Post Implementation Issues Thread-Index: AcSIkZsXInnMu9+GQ9CXF5j+KPCZ0AAgMjfAAARG+zAAALbvwA== From: To: , X-OriginalArrivalTime: 23 Aug 2004 15:33:26.0800 (UTC) FILETIME=[88ACB900:01C48926] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 8114 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Paula_Stankus@doh.state.fl.us Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Okay,=20 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@freelists.org' 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@freelists.org' 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: ORA-01002: fetch out of sequence 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@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 -----------------------------------------------------------------