Nested FOR cursor LOOPs, BASIC QUESTION
525853Feb 15 2007 — edited Feb 15 2007Hello,
I am trying to nest a For-loop using a cursor (see below), but the program is not entering the second (nested) for-loop. This program compiles fine, but during run-time, the nested loop does not execute. I'd rather not use FETCH statements and keep everything in place. I think the solution is rather trivial, but I'm new to PL/SQL. PLEASE HELP!!!!!
-----------------------------------------------------------
cursor c1 is
select coi_con_uid,coi_not_code,coi_closed_yn,coi_timestamp
from s_coi_con_issue
where coi_not_code = 'NOT107'
and coi_timestamp <= v_aweekago
and coi_closed_yn = 'N';
cursor c2 is
select tsk_uid
from s_tsk_task
where tsk_status in ('C')
and tsk_tty_code = 'CONTAC'
and tsk_date_end = '' FOR UPDATE;
BEGIN
select to_date(sysdate - 7) into v_aweekago from dual;
DBMS_OUTPUT.PUT_LINE('System date used is ' || v_aweekago);
FOR coi_row in c1 LOOP
v_tsk_cnt := 0;
v_coi_row_cnt := v_coi_row_cnt + 1;
v_con_uid := rtrim(to_char(coi_row.coi_con_uid));
v_tsk_act_str := ('"CON_UID","' || v_con_uid || '"') ;
DBMS_OUTPUT.PUT_LINE('COI_CON_UID: ' || v_con_uid);
DBMS_OUTPUT.PUT_LINE('v_tsk_act_str: ' || v_tsk_act_str);
-----The Program is not entering into this Loop
FOR tsk_row in c2 LOOP
v_update_cnt := v_update_cnt + 1;
update s_tsk_task
set tsk_status = 'A'
where tsk_uid = tsk_row.tsk_uid and
tsk_action_string = v_tsk_act_str;
DBMS_OUTPUT.PUT_LINE('----Task updated');
END LOOP;
END LOOP;
COMMIT;