PL/SQL Stored Procedure Cursor Tuning

From: Heidi Huber <hhuber_at_enlighten.com>
Date: 20 Feb 2002 10:39:51 -0800
Message-ID: <c8d38a43.0202201039.7d295e4b_at_posting.google.com>

I've written a stored procedure (below) which will duplicate a large amount of linked plan data. It is run only once per year from within my Oracle development tool (Toad). When I run only a few plans (see first cursor, cDental), it works fine. When I run a large number of plans to duplicate, however, I get a rollback too old/segment too small error (ORA-01555).

My question is if I'm using the cursors correctly. All the select cursors should only grab a snapshot of the data as it was when the stored procedure was first called (the cursor should not update based on inserted data). I don't want to change the rollback segment size since I'm working on a development copy of a client's database.

Any suggestions would be much appreciated!

Thanks in advance,

Heidi Huber
Internet Developer

CREATE OR REPLACE PROCEDURE duplicate_plans (   status OUT VARCHAR2)
IS
  CURSOR cDental IS

	SELECT PLANID,a,b,c,d,e,f,g,h,i,j
	FROM PLANS
	WHERE a IN (1,2,3,4,....); -- Number of plans can change

 va NUMBER;
 vb VARCHAR2(100);
 vc NUMBER;
 vd VARCHAR2(1024);

 ve NUMBER;
 vf NUMBER;
 vg NUMBER;
 vh VARCHAR2(80);

 vi VARCHAR2(300);
 vj VARCHAR2(100);
 vPlanYear VARCHAR2(20);
 vOldPlanId NUMBER;
 vNewPlanId NUMBER;
 vOldPtoPIId NUMBER;
 vNewPtoPIId NUMBER;
BEGIN
   status := '0';
   vPlanYear := '2002';          

   OPEN cDental;
   LOOP

      FETCH cDental INTO vOldPlanId,va,vb,vc,vd,ve,vf,vg,vh,vi,vj;
      EXIT WHEN cDental%NOTFOUND;
	    	
      INSERT INTO PLANS (a,b,c,d,e,f,g,h,i,j,PLANYEAR)
         VALUES (va,vb,vc,vd,ve,vf,vg,vh,vi,vj,vPlanYear);
      COMMIT;
		
      SELECT SEQ_PLANID.CURRVAL INTO vNewPlanId FROM DUAL;
			
      FOR cPCStoP1 IN (SELECT * FROM SETTINGS1 WHERE PLANID =
vOldPlanId) LOOP
         INSERT INTO SETTINGS1 (a,PLANID)
            VALUES (cPCStoP1.a,vNewPlanId);
         COMMIT;  
      END LOOP;
				
      FOR cPCStoP4 IN (SELECT * FROM SETTINGS4 WHERE PLANID =
vOldPlanId) LOOP
         INSERT INTO SETTINGS4 (a,PLANID)
            VALUES (cPCStoP4.a,vNewPlanId);
         COMMIT;  
      END LOOP;

      FOR cHtoP IN (SELECT * FROM HTOPLAN WHERE PLANID = vOldPlanId)
LOOP
         INSERT INTO HTOPLAN (a,PLANID)
            VALUES (cHtoP.a,vNewPlanId);
         COMMIT;
      END LOOP;
				
      FOR cPtoPI IN (SELECT * FROM PLANITEMS WHERE PLANID =
vOldPlanId) LOOP
         vOldPtoPIId := cPtoPI.PTOPI;
         INSERT INTO PLANITEMS (PLANID,a)
            VALUES (vNewPlanId,cPtoPI.a);
         COMMIT;

         SELECT SEQ_DP_TO_PI.CURRVAL INTO vNewPtoPIId FROM DUAL;

         FOR cPtoPItoF IN (SELECT * FROM FOOTNOTES WHERE PTOPI =
vOldPtoPIId) LOOP
            INSERT INTO FOOTNOTES (PTOPI,a)
               VALUES (vNewPtoPIId,cPtoPItoF.a);
            COMMIT;
         END LOOP;
				
         FOR cSettings IN (SELECT * FROM SETTINGS WHERE PTOPI =
vOldPtoPIId) LOOP
            INSERT INTO SETTINGS (PTOPI,a,b)
               VALUES (vNewPtoPIId,cSettings.a,cSettings.b);
            COMMIT;
         END LOOP;
				
      END LOOP;

   END LOOP;             CLOSE cDental;

   EXCEPTION

      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('Other Exception : sqlcode ' || sqlcode
|| ' sqlerrm ' || sqlerrm);
         status := '-100';
      RETURN;

END;
/ Received on Wed Feb 20 2002 - 19:39:51 CET