Re: PL/SQL Stored Procedure Cursor Tuning

You are probably committing too often! There have always been issues with fetching across commits which is what you are doing.

A snippet from Oracle Website.

'ORA-1555 usually occurs when the rollback segment entries get overwritten and a long running select statement is not able to form a read consistent image of the data as it existed when the select statement began its execution'

2 things you can try

• Committing only at the end of your main for loop or set up some kind of counter and commit only after every 100 records or so.
• Instead of using an OPEN - FETCH- CLOSE sequence for your cursor try using a FOR loop with your cursor as a condition, that way, oracle does an implicit fetch.

e.g

    FOR REC IN cDental LOOP                      

       INSERT INTO PLANS (a,b,c,d,e,f,g,h,i,j,PLANYEAR)
          VALUES (rec.a,rec.b,rec.c,rec.d,rec.e,
                  rec.f,rec.g,rec.h,rec.i,rec.j,vPlanYear);

       ..
       ..
       ..
       etc
       ..
       ..

        COMMIT;

     END LOOP;

Try it.

HTH, M

hhuber_at_enlighten.com (Heidi Huber) wrote in message news:<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 Thu Feb 21 2002 - 11:43:12 CET