Why my PLSQL code not looping by using while loop?

Question

Here is the code. Basically the ICRTASKREFERENCE table has 31 rows I need to loop in. So the loop should runs 31 times, but for now it only runs once. Can someone help me this? much appreciate!

create or replace PROCEDURE IC_CREATE_TASKS_P AS 
    startnumber number:=1;
    maxCount number;
    taskName varchar(255);
    BEGIN
        SELECT count(*) INTO maxCount from ICRTASKREFERENCE;
         WHILE startnumber <= maxCount
            LOOP
                SELECT TASKCODE INTO taskName from ICRTASKREFERENCE WHERE TASKREFERENCEID = startnumber;
                taskName := UPPER(taskName);
                BEGIN
                    EXECUTE IMMEDIATE 'INSERT INTO ictask (
                        STATUS,
                        CATEGORY,
                        TASKCODE,
                        TASKTYPE,
                        SOURCETABLECODE,
                        MODIFIEDON,
                        MODIFIEDBY,
                        CREATEDON,
                        PRIORITY
                    ) 
                    SELECT 
                        ''OPEN'',
                        taskReferenceTable.CATEGORY,
                        taskReferenceTable.TASKCODE,
                        taskReferenceTable.TASKTYPE,
                        taskReferenceTable.SOURCETABLECODE,
                        SYSDATE,
                        ''Administrator'',
                        SYSDATE,
                        taskReferenceTable.PRIORITY
                     FROM (SELECT * from '||taskName||' WHERE NEWFLAG=1) newFlag cross join (SELECT * from ICRTASKREFERENCE WHERE TASKREFERENCEID = '||startnumber||') taskReferenceTable WHERE newFlag.MSTID NOT IN 
                     (SELECT SOURCETABLEID FROM ICTASK)';

                END;

                     startnumber := startnumber + 1;
            END LOOP;
    END;

What proof do you have that loop executed only once? Hint: Insert into .. select do not insert anything if select returns no rows. — Popeye
– Popeye, Commented Mar 11, 2020 at 20:10
@Tejash I see the task table finally only has the result for first taskcode. The taskcode is from ICRTASKREFERENCE table — PEIRAN LIU
– PEIRAN LIU, Commented Mar 11, 2020 at 20:25
Loop runs as many times as there are rows in ICRTASKREFERENCE table. So, how many rows does it have? — Littlefoot
– Littlefoot, Commented Mar 11, 2020 at 20:30
@Littlefoot the ICRTASKREFERENCE has 31 rows, so the loop should run 31 times — PEIRAN LIU
– PEIRAN LIU, Commented Mar 11, 2020 at 20:35

Littlefoot · Accepted Answer · 2020-03-11 20:34:01Z

2

Simpler code would be this:

begin
  for cur_r in (SELECT rownum as startnumber,
                       TASKCODE as taskName 
                from ICRTASKREFERENCE
               ) loop
    execute immediate ... (your dynamic SQL goes here)
  end loop;
end;

Make sure that tables you use in dynamic SQL actually exist, as well as corresponding TASKREFERENCEID column value.

answered Mar 11, 2020 at 20:34

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why my PLSQL code not looping by using while loop?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related