A little help debugging this procedure, please

From: DFS <nospam_at_dfs_.com>
Date: Sun, 28 Aug 2005 04:50:41 -0400
Message-ID: <BYeQe.6248$Yh6.4077@fe04.lga>

I'm not quite getting it. It compiles with errors, and won't run.

It contains nested cursors, with the inner one (cursor2) using values from the outer one (cursor1). Probably the cursor2 should be declared outside the first FOR...LOOP?

---

CREATE OR REPLACE PROCEDURE SetEventNbr (FacID INTEGER) IS BEGIN DECLARE
i NUMBER := 1;
j NUMBER := 1;
execObject VARCHAR2(200);
objectCursor INTEGER;

CURSOR cursor1 IS
 SELECT FIELD1
 FROM TABLE
 WHERE FIELD2 = || FacID ||

 FOR cObject IN cursor1 LOOP
  execObject := 'UPDATE TABLE SET FIELD4 = ' || i || ' WHERE FIELD1 = ' || cObject.FIELD1 || ';
  DBMS_SQL.PARSE(DBMS_SQL.OPEN_CURSOR, execObject, DBMS_SQL.NATIVE);

   CURSOR cursor2 IS
    SELECT FIELD1
    FROM TABLE WHERE FIELD2 = ' || FacID || ' AND FIELD3 = ' || cObject.FIELD1 || ';

    j := 1;
    FOR cObject2 in cursor2 LOOP
     execObject := 'UPDATE TABLE SET FIELD4 = || (i + (j / 100)) || WHERE FIELD1 = ' || cObject2.FIELD1 || ';

     DBMS_SQL.PARSE(DBMS_SQL.OPEN_CURSOR, execObject, DBMS_SQL.NATIVE);
     j := j + 1;

    END LOOP;    i := i + 1;
 END LOOP;
 END SetEventNbr;

---

Thanks for your help Received on Sun Aug 28 2005 - 03:50:41 CDT